Convert Comma Separated Values into Rows

Question

Convert Comma Separated Values into Rows

heyzain

Old Hand

Points: 351
More actions
January 6, 2011 at 10:10 am

#149642

Folks,
I have a table (Table1) with 2 columns (ID, Data) having data like this:
ID Data
----- ----------
1 data1,data2,data3
1 data2,data4
2 data2,data3
3 data1,data4,data3
I need the comma separated values in Data column to be converted into rows. Below is the output I require:
ID Data
----- ---------
1 data1
1 data2
1 data3
1 data4
2 data2
2 data3
3 data1
3 data4
3 data3
Please Help.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

See this post, I think it answers your question

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WayneS SSC Guru Points: 95409 More actions · Answer 2

Just to make sure I understand, since ID 1 has "data2" in both rows, you want it returned only for the first row, and you want all the data ordered in FIFO?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95409 More actions · Answer 3

The DelimitedSplit8K function (Click here for the latest Delimited Split Function) will do this for you. It is probably the absolute fastest method that you'll find for splitting strings.

I have some code that comes very close to your required output, but it's not removing that duplicate "data2" for ID 1. I'm still working on that. In the meantime, in order to help us help you, please check out the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95409 More actions · Answer 4

The ~~attached~~ below code produces your desired results. ~~(having trouble posting code directly from work - I'll move it into the post tonight!)~~

-- See how this starts off with a table and data in it?

-- If you had provided us the data in this format,

-- it would have made things easier for all of the

-- volunteers on this site to help you out.

declare @Table1 table (ID int, data varchar(500));

insert into @Table1

select 1, 'data1,data2,data3' UNION ALL

select 1, 'data2,data4' UNION ALL

select 2, 'data2,data3' UNION ALL

select 3, 'data1,data4,data3';

WITH CTE AS

(

-- Assign a sequential row number to each ID, restart with each change in ID

SELECT t1.ID, t1.data, RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY data)

FROM @Table1 t1

), CTE2 AS

(

-- assign a sequential row number to each ID/item, restart with each change in ID/item

SELECT t1.ID, ds.Item, RN, ds.ItemNumber, RN2 = ROW_NUMBER() OVER (PARTITION BY t1.ID, ds.ITEM ORDER BY t1.ID, t1.RN, ds.ItemNumber)

FROM CTE t1

CROSS APPLY dbo.DelimitedSplit8K(t1.data, ',') ds

)

-- get the results, only getting the first item for each id.

SELECT ID, Item

FROM CTE2

WHERE RN2 = 1

ORDER BY ID, RN, ItemNumber;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

heyzain Old Hand Points: 351 More actions · Answer 5

Hats off to you Wayne. Your solution worked exactly as i required.

Thank you very much 🙂

WayneS SSC Guru Points: 95409 More actions · Answer 6

You're welcome. Do you understand it? (See the note in my signature...)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2