Split one column into multiple row for comma delimited data


Data in column Items in this format:
id1 id2     Items
  1   1       aa,bbb,ccc,ddd
  1   2       xx,yy,zzz
 
Output:
1 1 aa
1 1 bbb
1 1 ccc
1 1 ddd
1 2 xx
1 2 yy
1 2 zzz
 

SELECT a.id1, a.id2, a.Items, b.Value FROM dbo.Test AS a

CROSS

APPLY fn_Split(a.Items, ‘,’) AS b

We can create an SSIS package to do this with the Wizard:

Execute SQL Task>>Dataflow Task (From OLE DB Source > Destination : tableName)

 

Another way to so this through a number table (see Note 1):

SELECT

id1,id2,items,

SUBSTRING

(items, n, CHARINDEX(‘,’, items + ‘,’, n) n) AS item, n + 1 LEN(REPLACE(LEFT(items, n), ‘,’, )) AS item_idx

FROM

Test AS P

CROSS

JOIN ( SELECT number FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY newid()) as number FROM sys.objects ) t

WHERE number <= 100 ) AS Numbers(n)

WHERE

SUBSTRING(‘,’ + items, n, 1) = ‘,’ AND n < LEN(items) + 1

ORDER

BY id1, id2, item_idx

 

One more solution  with CTE from Itzik Ben-Gan: I have developed a sample with his solutions.

http://jingyang.spaces.live.com/blog/cns!CC21A118B1B5250!252.entry

 

 
Reference:
Cross Apply:
http://www.mydatabasesupport.com/forums/sqlserver-programming/383420-problem-inline-table-valued-udf-some-sort-join-onparameters-wanted.html
Split Function:
http://www.odetocode.com/articles/365.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
Note 1: A solution provided by Plamen Ratchev from this link:
http://www.eggheadcafe.com/software/aspnet/31741612/split-column-into-multipl.aspx
and link to create a number table:
http://www.projectdmx.com/tsql/tblnumbers.aspx
 
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s