Tough T-SQL puzzle - SQL Server Forums

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)

Tough T-SQL puzzle

Author

Topic

ComputerMike
Starting Member

18 Posts

Posted - 2008-07-30 : 11:56:32

i have data in source table that needs to be transformed into destination table. Each source row can contain data for multiple rows in destination. Source column contains CSV data. Never done much looping in SQL

example

case # code
200 A,B,C
300 D,E

transformed to

case# code
200 A
200 B
200 C
300 D
300 E

need to do with T-SQL

thanks

Mike

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-30 : 12:29:31

Do a search for a SPLIT function, this has been solved many times before on this forum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 12:58:55

create a udf like this

CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val int  
)  
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

and use it on your table

SELECT t.[case #],b.Val
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.code)b

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-30 : 21:11:42

see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources