Split columns list values in several rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Split columns list values in several rows

Author

Topic

CL
Starting Member

1 Post

Posted - 2010-07-13 : 14:19:41

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 14:55:30

From visahk


CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(max)  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

select t.itemID, t.Name, b.Val 
  FROM givedataplease t
CROSS APPLY ParseValues(t.RequestedBy)b

Result
1	shoes	john
1	shoes	 mark
1	shoes	 bob
2	tops	mark
2	tops	 bob
3	bags	john
3	bags	 mary

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-07-14 : 05:22:45

try this one too


DECLARE @str TABLE(itemID int, Name varchar(32), RequestedBy varchar(64))
insert into @str select 1 , 'shoes' , 'john, mark, bob' union all select 
2 , 'tops' , 'mark, bob' union all select 
3 , 'bags' , 'john, mary'

SELECT		 itemid,s.name,
		REPLACE(SUBSTRING(s.RequestedBy,charindex(',',s.RequestedBy,v.number),abs(charindex(',',s.RequestedBy,charindex(',',s.RequestedBy,v.number)+1)-charindex(',',s.RequestedBy,v.number))),',','')as value
FROM		 @str AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
			AND v.number > 0
			AND v.number <= len(s.RequestedBy)
WHERE		substring(',' + s.RequestedBy, v.number, 1) = ','

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 05:41:53

"I cannot change the database fields types"

Not that it will help much with this problem (although it would avoid some CASTING and possible loss of data is the delimited list in the column could be longer than 4000 characters),
but given that you are in SQL 2005 you really ought to change, or get ThePowersThatBe to change, the datatype from NText to NVarchar(MAX).

I don't know if NText was always stored out-of-page (i.e. even for small sized values), but NVarchar will be stored in-page whenever space permits
- and certainly for the small examples you have given - which will improve performance etc. as well as giving you access to ordinary string handling functions without
messing about with casting and deciding how-the-heck to handle things that are longer than 4,000 Nchars

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources