Advertisements
Home > SQL Tips > Combine multiple ROWS to CSV String… and vice-versa

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


Advertisements
  1. April 30, 2014 at 8:37 pm

    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

    • August 17, 2015 at 3:49 pm

      thanks @jaikey, I appreciate that you find this article valuable.

  2. March 22, 2013 at 5:29 pm
    • August 17, 2015 at 3:50 pm

      sorry @souleymane, could not respond you in time. Hope you’d resolved this by now.

      • August 24, 2015 at 8:30 pm

        Yes long time ago. few suggestions received but an adjusted “for xml path” did work for me . Thanks anyway coming back

  3. March 22, 2013 at 5:14 pm

    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

  4. sachinpatil10d
    September 20, 2011 at 12:56 pm

    Nice article

  5. February 18, 2011 at 12:20 pm

    Thanks Peter for you your comments.
    Yes, the query provided by me was not backward compatible for SQL erlier versions < 2005.

  6. Peter Elzinga
    February 18, 2011 at 12:09 pm

    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

  7. November 25, 2010 at 3:03 pm

    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.

  8. J.D.
    November 25, 2010 at 8:36 am

    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

  9. November 9, 2010 at 7:52 am

    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

  10. October 14, 2010 at 8:17 am

    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)

  1. March 10, 2016 at 7:01 am
  2. November 6, 2013 at 5:20 pm
  3. January 2, 2011 at 10:31 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: