Combine multiple ROWS to CSV String… and vice-versa
There are times when you want to combine values from multiple rows of a single column into a CSV (Comma Separates Value) String.
Like combining multiple orders of each customer into one single row, or multiple Cities against each State as shown below.
It was a bit difficult to do in SQL Server 2000 and requires to create a customized function or a script.
But with SQL Server’s 2005 version, the new “PATH” mode with “FOR XML” provides us a way to get the desired output. Its a bit tricky but will do the needful, that’s why I’ve also added this post for my reference as well.
CREATE TABLE #tempCityState (State VARCHAR(5), City VARCHAR(50)) INSERT INTO #tempCityState SELECT 'CO', 'Denver' UNION SELECT 'CO', 'Teluride' UNION SELECT 'CO', 'Vail' UNION SELECT 'CO', 'Aspen' UNION SELECT 'CA', 'Los Angeles' UNION SELECT 'CA', 'Hanford' UNION SELECT 'CA', 'Fremont' UNION SELECT 'WA', 'Seattle' UNION SELECT 'WA', 'Redmond' UNION SELECT 'WA', 'Bellvue' SELECT * FROM #tempCityState
State City
CA Hanford
CA Fremont
CA Los Angeles
CO Denver
CO Aspen
CO Vail
CO Teluride
WA Seattle
WA Redmond
WA Bellvue
Using the “FOR XML PATH” syntax:
SELECT DISTINCT State, (SELECT SUBSTRING((SELECT ',' + City FROM #tempCityState WHERE State = t.State FOR XML PATH('')),2,200000)) AS Cities INTO #tempCityStateCSV FROM #tempCityState t -- OR -- SELECT DISTINCT State, (SELECT STUFF((SELECT ',' + City FROM #tempCityState WHERE State = t.State FOR XML PATH('')),1,1,'')) AS Cities FROM #tempCityState t SELECT * FROM #tempCityStateCSV
State Cities
CA Hanford,Fremont,Los Angeles
CO Denver,Aspen,Vail,Teluride
WA Seattle,Redmond,Bellvue
Now, converting it back to multiple rows:
SELECT A.[State], Split.a.value('.', 'VARCHAR(100)') AS City FROM (SELECT [State], CAST ('<M>' + REPLACE([Cities], ',', '</M><M>') + '</M>' AS XML) AS String FROM #tempCityStateCSV) AS A CROSS APPLY String.nodes ('/M') AS Split(a) ORDER BY 1,2
State City CA Fremont CA Hanford CA Los Angeles CO Aspen CO Denver CO Teluride CO Vail WA Bellvue WA Redmond WA Seattle
Categories: SQL Tips
CSV String, FOR XML PATH, ROWS to CSV, XML
Hello,your script in "Now, converting it back to multiple rows:" is not working because this website removes XML tags from example. I had to view HTML source to make it work ;)Also in last line there should be 'M' in lowercase:CROSS APPLY String.nodes ('/m') AS Split(a)
Thanks Tomas,
I actually imported my Blogger blog (http://sqlwithmanoj.blogspot.com) to WordPress, just sometimes back. Didn’t realized this.
Thanks for finding and commenting this out.
~Manoj
hi thanks for your posting..
I am just coufused with this script
SELECT DISTINCT State, (SELECT SUBSTRING((SELECT ‘,’ + City
FROM #tempCityState
WHERE State = t.State
FOR XML PATH(”)),2,200000)) AS Cities
INTO #tempCityStateCSV
FROM #tempCityState t
can you please teach me how does this code works?
in this part
(SELECT SUBSTRING(
( SELECT ‘,’ + City
FROM #tempCityState
WHERE State = t.State
FOR XML PATH(”)
),2,200000)
) AS Cities
→ SUBSTRING( (select ‘,’ + city from …) ,2,200000)
why should we substring from 2 to 200000
what the purpose means? thank you very much
About how this code works is related to XML, as you can see the FOR XML PATH syntax.
When you use this statement:
select ‘,’+City from #tempCityState where State = ‘CO’ for xml path(”)
… this gives you: AspenDenverTelurideVail
… and when you change this statement to:
select ‘,’+City from #tempCityState where State = ‘CO’ for xml path(”)
… it gives: ,Aspen,Denver,Teluride,Vail
SUBSTRING is used to remove the comma (,) at first position.
I used 200000 just as an imaginary value as my CSV string could grow upto any length.
Well you can also use STUFF instead of SUBSTRING, like:
SELECT DISTINCT State, (SELECT STUFF((SELECT ‘,’ + City
FROM #tempCityState
WHERE State = t.State
FOR XML PATH(”)),1,1,”)) AS Cities
FROM #tempCityState t
Thanks for pointing it out.
You can do this also by using the query below.
Advantage is that it also works on older versions of SQL (2000 and below).
Disadvantage is that you have to specify n-1 LEFT JOINS where n is the maximum number of values you will have in the comma-separated list.
CREATE table #temp (col1 nvarchar(10), col2 nvarchar(1))
INSERT INTO #temp values(‘ComputerA’, ‘C’)
INSERT INTO #temp values(‘ComputerA’, ‘D’)
INSERT INTO #temp values(‘ComputerA’, ‘E’)
INSERT INTO #temp values(‘ComputerB’, ‘C’)
INSERT INTO #temp values(‘ComputerC’, ‘C’)
INSERT INTO #temp values(‘ComputerC’, ‘D’)
SELECT x1.col1
, REPLACE(ISNULL(MIN(x1.col2), ‘ ‘) + ‘, ‘
+ ISNULL(MIN(x2.col2), ‘ ‘) + ‘, ‘
+ ISNULL(MIN(x3.col2), ‘ ‘)
, ‘, ‘, ”) AS driveletters
FROM #temp x1
LEFT JOIN #temp x2
ON x1.col1 = x2.col1
AND x2.col2 > x1.col2
LEFT JOIN #temp x3
ON x1.col1 = x3.col1
AND x3.col2 > x2.col2
GROUP BY x1.col1
DROP TABLE #temp
Kind regards,
Peter Elzinga
Thanks Peter for you your comments.
Yes, the query provided by me was not backward compatible for SQL erlier versions < 2005.
Nice article
Nice post Manoj, how about if we want to get new line feed carriage after each item i.e output like this (3 rows exactly )
State City
——————
AK Nashville
Wynne
CA Fremont
Hanford
Los Anggeles
CO Aspen
Denver
Teluride
Vail
Do you have a workaround .. how been struggling with such issue for days now
sorry good image link http://www.flickr.com/photos/14261289@N02/8580154328/
sorry @souleymane, could not respond you in time. Hope you’d resolved this by now.
Yes long time ago. few suggestions received but an adjusted “for xml path” did work for me . Thanks anyway coming back
After searching a lot on the net. I Found this article that helped me very much. I had to convert comma separated value field into multiple rows. Thank you for putting this article sir
thanks @jaikey, I appreciate that you find this article valuable.