Converting a single comma separated row into multiple rows

May 20, 2010

Ever since I wrote Converting multiple rows into a single comma separated row, I was trying to find a SQL command which will do the reverse, which is converting the single comma separated row back to multiple rows. I checked up a few blogs and I found out that it was possible to do with the help of custom functions or stored procedures, but I was not interested in all of them. Finally I got the answer that I was looking for, and now that I did, I did not waste precious time in noting it down here, lest I forget. Let me try to show you what we are trying to achieve here with the help of an image

Convert comma seperated row to multiple rows

This can be done with the help of the below query

 SELECT A.[State],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [State],  
         CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

Again, no stored procedures or function, just plain old SQL 🙂

Update (23/05/2010) : I saw this blog by Brad Schulz (MVP) explaining this scenario and the performance implications in detail. So people who are serious about getting this in their code, please read his blog before you do so – Delimited String Tennis Anyone?

Posted by SQLJason

58 comments

Johan Bennink

Tried and tested. Very nice, I was using a stored procedure and string parsing to do this. I will have to look into the XML support in SQL this is very nice indeed.

You are the best…. MAN

priyank keshri

i have some values like this “DADRA & NAGAR HAVELI,Dadra” which includes spaces .

what should I do with this??

Looked all over for this, yours is 100x simpler than the next closest I found, good job

Excellent post. Thanks a lot ! Simplest solution on the internet 🙂

i cannot use group by clause by using this code

Could you explain your exact scenario? I am able to use a group by clause as shown in the query below
SELECT state,
COUNT(*)
FROM (SELECT a.[State],
split.a.VALUE('.', 'VARCHAR(100)') AS string
FROM (SELECT [State],
CAST ('' + REPLACE([City], ',', '') + '' AS
XML)
AS string
FROM tablea) AS a
CROSS APPLY string.NODES ('/M') AS split(a)) a
GROUP BY state

Above query not working ,you are using group by clause

Thanks for replying.
Is that possible to use group by clause for City.

thanks

thanks

Hi Guys,

Just wanted to extend a little for the above code.

Let's say below is the Data in a given table

ID Col1 Col2 Col3 Col4
1 111,112,113 2010-01-01,2010-01-15,2010-01-30 Books Pencils
2 114,115 2011-01-01,2011-01-15,2011-01-30 Chicago Altlanta
3 116,117 2012-01-01,2012-01-15,2012-01-30 Sky Moon

and the OUTPUT should like this below. with 1-1 correspondence for the separated commas in the data.

ID Col1 Col2 Col3 Col4
1 111 2010-01-01 00:00:00.000 Books Pencils
1 112 2010-01-15 00:00:00.000 Books Pencils
1 113 2010-01-30 00:00:00.000 Books Pencils
2 114 2011-01-01 00:00:00.000 Chicago Altlanta
2 115 2011-01-15 00:00:00.000 Chicago Altlanta
3 116 2012-01-01 00:00:00.000 Sky Moon
3 117 2012-01-15 00:00:00.000 Sky Moon

the below code can be used.

CREATE TABLE #Table (ID int IDENTITY(1,1),Col1 nvarchar(100),Col2 nvarchar(100),Col3 nvarchar(100),Col4 nvarchar(100))
CREATE TABLE #Table2 (ID int ,Col1 XML,Col2 XML,Col3 nvarchar(100),Col4 nvarchar(100))
CREATE TABLE #Table3 (ID int,ImpID int IDENTITY(1,1), Col1 nvarchar(100),Col3 nvarchar(100),Col4 nvarchar(100))
CREATE TABLE #Table4 (ID int,ImpID int IDENTITY(1,1), Col2 datetime,Col3 nvarchar(100),Col4 nvarchar(100))
CREATE TABLE #Table5 (ID int,ImpID int,Col1 nvarchar(100),Col3 nvarchar(100),Col4 nvarchar(100))
CREATE TABLE #Table6 (ID int,ImpID int,Col2 datetime,Col3 nvarchar(100),Col4 nvarchar(100))

INSERT INTO #Table VALUES('111,112,113','2010-01-01,2010-01-15,2010-01-30','Books','Pencils')
INSERT INTO #Table VALUES('114,115','2011-01-01,2011-01-15,2011-01-30','Chicago','Altlanta')
INSERT INTO #Table VALUES('116,117','2012-01-01,2012-01-15,2012-01-30','Sky','Moon')

INSERT INTO #Table2
SELECT
ID
,CAST ('' + REPLACE([Col1], ',', '') + '' AS XML)
,CAST ('' + REPLACE([Col2], ',', '') + '' AS XML)
,Col3
,Col4
FROM
#Table

Declare @ID int
Declare @MaxID int

SET @ID = (SELECT MIN(ID) FROM #Table)
SET @MaxID = (SELECT MAX(ID) FROM #Table)

WHILE (@ID <= @MaxID)

BEGIN

INSERT INTO #Table3(ID,Col1,Col3,Col4)
SELECT pvs.ID as ID
,Col1 = p.p.value('.', 'nvarchar(100)')
,Col3
,Col4
FROM
#Table2 pvs
CROSS APPLY pvs.Col1.nodes('//*') p(p)
WHERE
pvs.ID = @ID

INSERT INTO #Table4(ID,Col2,Col3,Col4)
SELECT pvs.ID as ID
,Col1 = p.p.value('.', 'datetime')
,Col3
,Col4
FROM
#Table2 pvs
CROSS APPLY pvs.Col2.nodes('//*') p(p)
WHERE
pvs.ID = @ID

INSERT INTO #Table5
SELECT ID,ImpID,Col1,Col3,Col4 FROM #Table3
WHERE
ID = @ID

INSERT INTO #Table6
SELECT ID,ImpID,Col2,Col3,Col4 FROM #Table4
WHERE
ID = @ID

TRUNCATE TABLE #Table3
TRUNCATE TABLE #Table4

SET @ID = @ID +1

END

–END

–INSERT INTO #

SELECT * FROM #Table
–SELECT * FROM #Table2
–SELECT * FROM #Table5
–SELECT * FROM #Table6

—-Main Output

SELECT
a.ID,a.Col1,b.Col2,a.Col3,b.Col4
FROM
#Table5 a
JOIN #Table6 b ON a.ID = b.ID
AND a.ImpID = b.ImpID

DROP TABLE #Table
DROP TABLE #Table2
DROP TABLE #Table3
DROP TABLE #Table4
DROP TABLE #Table5
DROP TABLE #Table6

THNX BRO

Manju Redrowthu

HI Jason,

I need exact logic in DAX. Scenario as follows..

I have a parameter (Text data type) in which User can give either student name or student number, Accordingly I have added a filter in Calculatetable as below

FILTER(Student,find(lower(@StudentNameNumber), lower(Student[StudentName]), 1, -1) > -1 || find(lower(@StudentNameNumber), lower(Student[StudentNumber]), 1, -1) > -1)

Now, my requirement is

StudentNameNumber Parameter should now accept multiple values separated by comma…

for example john,smith or 23,24

there is no function in Dax as such to separate a string into sub strings.

any ideas???

Can you see if this post answers your question – http://fendy-huang.blogspot.com/2012/09/create-ssrs-report-using-dax.html If not, can you send a detailed question (with some sample data) to jason143@gmail.com of what you want to achieve?

Manju Redrowthu

Thanks for sharing the Url but Pattern Matching is missing in that

for example if my input is Joh,SMI then my
required output is to display the details of john and smith

Pattern matching is achieved by FIND() function in DAX and is not in PATHCONTAINS() function

need help..

Can you send the detailed requirements to my email address listed above? (if you have an excel workbook with some data in it, I would be able to reply to you faster)

योगेश चन्द्र उप्रेती

Thanks a lot sir Hurrey. That was i really need it.

Thank you man. You are great!

It Saved for me 🙂

Dalibor Puljiz

Thanks, great solution 🙂

Very Nice 🙂 Its working perfectly .. Thanks 🙂

Igor Kuzenkov

Thank you very very very much Man! Perfect!

Oracle Apex Solutions

Hi Jason,

Thanks for your post.

I have the same requirement at my end and was trying the SQL you have shared but I am getting few errors:

1. Split.a.value (cannot find either column "split" or user defined function or aggregate "split.a.value".)

2. String.nodes (invalid object name "String.nodes")

Please suggest what can be the issue.

Database: SQL Server 2008.

Regards,
Tauceef

Hi,
The Error same as Oracle Apex Solutions is appearing to me also.. Can u plz tell how can i resolve it..

Mansi Chaudhari

declare @booking varchar(50)='15474,15480,1500,12342,456456,123444,3465478709'
declare @NoOfChar decimal(18,0)
declare @book as table (id decimal(18,0))
declare @value varchar(50)
set @NoOfChar = LEN(@booking)
while(@NoOfChar !=0)
begin
declare @g varchar(max)
SET @g = LEN(@booking) – LEN(LEFT(@booking, CHARINDEX(',',@booking+',')-1))
if(@g=0)
begin
set @booking=@booking+','

set @value=substring(@booking,1,PATINDEX('%,%',@booking)-1)
insert into @book values(@value)
select * from @book
end
else
begin
set @value=substring(@booking,1,PATINDEX('%,%',@booking)-1)
insert into @book values(@value)
end
set @booking= STUFF(@booking,1,PATINDEX('%,%',@booking),'')
set @NoOfChar=@g
end

This is excellent, exactly what I needed. Thank you!

Thank you…

Thank you, you solved lot of my issue in this matter, thanks a lot

Thanks, great solution

Vijay krishnan

Hi,

I have a comma seperated string @input = ‘Test1, test2, test3’. How to insert this string as a individual record in the table.

it worked with me as Sql… but not Oracle what can I do… 🙁

oracle doesn’t accept cross apply

The query at the top of the page works great except for when I have a State but no City. I would like to have the City as null for this instance. What can I do? Thank you in advance

Can We Use the One Column name in Twice in the Above Split Query..? If it is Possible Please Share the query. its More Important to me. Already i Have Used but i getting the Error as Column name ‘XYZ’ is already Used… Thanx in Advance

Hi Jason, thanks for your post but following query is not working in SQL developer, moreover if I want to do on two columns what will be the query, please suggest… thanks

SELECT A.[State], Split.a.value(‘.’, ‘VARCHAR(100)’) AS String FROM (SELECT [State], CAST (” + REPLACE([City], ‘,’, ”) + ” AS XML) AS String FROM TableA) AS A CROSS APPLY String.nodes (‘/M’) AS Split(a); – See more at: http://sqljason.com/2010/05/converting-single-comma-separated-row.html#sthash.cf9Q84rr.dpuf

Hi,

The code works great for columns that contain a comma or other character you want to delimit by, but what about those values which are not delimited? How can a query encompass those as well in the same result.

Any help for PL/SQL code please ?

Daniel Marinho

This was hugely helpful. Congrats!

SELECT A.COMPANY,
Split.a.value(‘.’, ‘VARCHAR(20)’) AS string
FROM (SELECT COMPANY,CAST (” + REPLACE(EMPLOYEE_ID,’,’,”) + ” AS XML) AS A FROM EMPLOYEE_DETAILS);

For the above statement i am getting invalid datatype for XML. Please help me with this.

Thanks in advance.

Regards,
Vivek92

Hi,

I have following data in Excel. I want to convert semicolon separated cell into rows.

Id Name Status Contacts

1234 Test Tesing ACTV Apple Rose; Test Testing; Mtest Testy; Aaaaa Test; Deavc Test; Stest Ctest
1232 Test Easy ACTV Aasd Ntestl; Mtestt Btesttler; Htest Mtest; Testravis Kesting; Etest Nest

Results

Id Name Status Contacts

1234 Test Tesing ACTV Apple Rose
1234 Test Tesing ACTV Test Testing
1234 Test Tesing ACTV Mtest Testy
1234 Test Tesing ACTV Aaaaa Test
1234 Test Tesing ACTV Deavc Test
1234 Test Tesing ACTV Stest Ctest
1232 Test Easy ACTV Aasd Ntestl
1232 Test Easy ACTV Mtestt Btesttler
1232 Test Easy ACTV Htest Mtest
1232 Test Easy ACTV Testravis Kesting
1232 Test Easy ACTV Etest Nest

Thanks,

Great post. thank you!

You are the best…. MAN

Derek Broughton

B.You-ti-ful!

Who cares about the efficiency!

Hi Jason,
I tried above query but I am getting below error. Can you help me fix, please .
The replace function requires 3 argument(s).

Hi Jason,
I tried your above query but as for me I am getting the “The replace function requires 3 argument(s).” error. Would you advise please how I can fix this?

——————————–
SELECT A.[Product Type]
,A.[Validation]
,A.[Name]
,A.[Date Resolved]
,A.[Status]
,A.[Group]
,split.a.value(‘|’, ‘VARCHAR(100’) AS String
FROM (Select [Product Type],[Validation],[Name],[Date Resolved],[Status],[Group],
CAST (” + REPLACE([Order Type]), ‘,’, ”) + ” AS XML) AS String
FROM [MISC].[dbo].[V_products]) AS A CROSS APPLY String.nodes (‘/M’) AS Split(a)

Solid solution for me!! thank you author

This split query is really awesome!

Hi SQLJason ,

I am trying to get a fix to something of similar kind you have mentioned in the post. Below is my requirement .

Column names that have to be used : Infrastructure_Change_ID ( similar to state in ur example) and Justification ( example data: 1;2;4)

Below is designed query :

select A.Infrastructure_Change_ID,
split.A.VALUE(‘.’,’VARCHAR(100)’) AS string
from (select CHG_ChangeAPDetailSignature.Infrastructure_Change_ID,
CAST(”+REPLACE(CAST([Justification] as nvarchar(max)),’;’,”)+” AS XML)
AS string
from CHG_ChangeAPDetailSignature) AS A
CROSS APPLY string.NODES (‘/M’)
group by Infrastructure_Change_ID

I am not able to execute it . Could you please help me with this ?

The query simply does not work.

parenthesis do not line up.

SAKTHIVEL SUNDARAM

Hi Jason,
I have a data
VENDOR_INTERFACE_ID INVOICE_CURRENCY_CODE
1000 EUR,USD,INR,JPY

I want
1000 EUR
1000 USD
1000 INR

Your code does not work. Either the XML is invalid or you get a syntax error right away by simply cut and paste your code into a query window. Unexpected ‘A’ identifier.

Allison G Perea

THANK YOU SOOO MUCH! This has helped me tremendously!

Thanks for the code..it’s really helped 🙂

Works like a dream, just what I needed. Thanks for this, great job!

I want to insert two values in sql server one value is dropdown value another one is listbox values . In listbox multi values inserted , the values insert split rows . Which procedure is used solving this problem ??

Insert the Values in a Table from a String,
String is ‘1|2|3^2|3|4’

tblTestInsert
SrNo
V1
V2

Write a SQL Query

Leave a Reply