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
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?
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
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
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?
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 🙂
Thanks, great solution 🙂
Very Nice 🙂 Its working perfectly .. Thanks 🙂
Thank you very very very much Man! Perfect!
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..
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
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 ?
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
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.
Yup.
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.
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
Hi there. Your example is very enticing to use because it’s very portable. Even when SQL introduced SPLIT_STRING in 2016, many organizations still have older SQL Server installs so this comes in handy. A suggestion: Please include the creation of the table TableA.
Wow
I was looking for at way to evaluate the data of some csv input files. My evaluation is done using a view, testing and outputting the failed evaluations per line as an extra column. Since I’m facing csv-files with up to 10 M rows, performance is crucial. The extra error information column contains all the errors of the row, separated by a ” | ” separator.
However in order to group the errors correctly (more errors are tested after this view) for output I had to split up the errors, one error per row.
I just tested this code example with 1 M rows, and it finished in less than 2 minutes. Impressive.
I was using a cross apply solution before but it made my row-count explode. This solution combines the advance of cross apply and still keeps the row-count down to a fair level.
Thanks a lot.
Can u tell how to do this for multiple columns break
This coding is very useful,thanks
Very useful, worked. Many 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);
How can i run the same query in MySQL
Hi! How can I do exactly the same but for value that are separated with a TAB (and not the comma),
thnx
I want to separate (ex: abc$ghf$wqe$) in a multiple row ,
output:
abc
ghf
wqe
So how I can do this??
This is very nice and useful. Thanks.
I am getting error like “XML parsing: line 2, character 36, illegal name character”
Excellent !