The best way to learn something is to revisit some of the older blogs and then look at finding a more efficient way to work on the same problem. Recently I was looking at the forums to find a question come up again and again around making a comma separated list. This is not new over this blog and if you search you will get few. Long time ago I have written below blogs to show the values stored in table as comma separate list – this is not a rehash of what was written before but a newer way to solve the problem again.
SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP
SQL SERVER – Comma Separated Values (CSV) from Table Column
Recently someone asked me a reverse of that. They were storing comma separated values in a column of table. This table was getting populated from some source via SSIS package. Here is the simplified version of the problem I was presented.
DECLARE @t TABLE ( EmployeeID INT, Certs VARCHAR(8000) ) INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')
This is how data looks like in the table.
With the above data available, they want it split into individual values. Searching for possible solutions, here is the solution query I came up with:
SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
Output looks like below.
Hope this would help you in your project. Let me know if you are able to find a different solution to the same problem. The best way to learn is to learn from each other.
Reference: Pinal Dave (https://blog.sqlauthority.com)
90 Comments. Leave new
Hi Pinal,
I’ve been struggling with a finding a good solution for this until I found this post. Thank you!
Really awesome solution, good one
Very useful thanks, but its taking long time to provide the output with high values. is there any option to reduce it.
Trying to make this work, but get an error in SSIS that I’m using an invalid character in the CAST Expression.
Pinal,
How would this XML query look if there were two columns with an equal number of values whose values need to be split into rows?
For example, if there were a YearEarned field in addition to a cert field.
EmployeeID, Certs, YearsEarned
1, “B.E.,MCA, MCDBA, PGDCA”, “2006, 2010, 2011, 2015”
What I would want is:
1, B.E., 2006
1 MCA, 2010
1, MCDBA, 2011,
1, PGDCA, 2015
Thoughts?
Thanks,
Mike
Hi Pinal,
I have the same question as Mike, where more than one column is there with delimited values and how to make multiple columns to arrange their values row wise.
Could you please help ?
Any thoughts, Pinal?
@Mike
have you thought of using any of the code above in 2 parts, followed by inner join on EmpoyeeID?
1, B.E.
1, MCA
1, MCDBA
1, PGDCA
1, 2006
1, 2010
1, 2011
1, 2015
That’s not a bad idea. I can give it a try. I was just trying to see if there was a way to do it in one query.
Hi This is Prakash, I need requirement like this.
EmployeeID, Certs, YearsEarned
1, “B.E.,MCA, MCDBA, PGDCA”, “2006, 2010, 2011, 2015”
What I would want is: Single row with multiple lines in that column value
1, B.E., 2006
MCA, 2010
MCDBA, 2011,
PGDCA, 2015
Can anybody help on this.
Oh this is perfect! Thanks!
Excellent, Very Thanks, was perfect solution to my problem, already that in company where job are not authorized create function in database.
Sorry , my English is not good, I hope you understand the comment.
Your page and post are helpful .
Greetings.
Hi Pinal,
Thanks for your XML code, it works in my case.
But now i’m facing error while executing in one of the case where list is empty for some records. For further investigation i pass the record which has NULL value (AllBarCode) in where clause, it works there but while executing the same for all records it gives below error.
Query : SELECT PCBGUID, ALLBarCode AS ALLBarCode_Actual, LTRIM(RTRIM(m.n.value(‘.[1]’, ‘varchar(8000)’))) AS ALLBarCode, Lot FROM (SELECT PCBGUID, ALLBarCode, CAST(” + REPLACE(ALLBarCode, ‘,’, ”) + ‘…
Error Source: .Net SqlClient Data Provider
Error Message: XML-Analyse: Zeile 1, Zeichen 19, ungültiges XML-Zeichen.
Could you please let me know does something went wrong in this.
declare @str varchar(max) = ‘1,2,3,4,5,6’, @sql varchar(max)
select @sql = ‘select * from (values’ + ‘(‘ + replace(@str,’,’,’),(‘) + ‘)) as T(ID)’
exec (@sql)
You may have “&” in your xml formed. Please replace them with ;amp. It should work.
I’m not clear what this part of the code is doing, if anyone cares to explain.
m.n.value(‘.[1]’,’varchar(8000)’) . What is m.n.value()
Amazing Stuff. Worked a wonder for me,
can someone please explain how its working.
SELECT EmployeeID,
LTRIM(RTRIM(m.n.value(‘.[1]’,’varchar(8000)’))) AS Certs
FROM
(
SELECT EmployeeID,CAST(” + REPLACE(Certs,’,’,”) + ” AS XML) AS x
FROM @t
)t
CROSS APPLY x.nodes(‘/XMLRoot/RowData’)m(n)
Hi I have requirement like this
I have list of acceptable values separated by comma like True,False,Ok,Cancel
I have to check provided input say True,Ok presnet in acceptable values or not if yes return 1 else 0
Hi Pinal ,
I am new to sql , could you please suggest how the above query can be used if we have multiple columns having comma separated values.
Thank you ,
Prakhar
You can write function which can do that.
Thank you for your help. This worked perfectly for me. I just had to remove the “EmployeeID” from the top of the select statement.
That’s great to hear @C-Dub