SQL Select Multiple Columns into Comma-Separated List

I needed to select a field from multiple rows in one table into one column in another table.  I wanted the one column to be a comma-separated list as well.

One way to do this would be to use cursors, but the following post showed an easier way using XML Path:  http://bytes.com/topic/sql-server/answers/431513-getting-data-multiple-rows-into-one-column.

I ended up doing something like this:

SELECT  State.Name,
       (SELECT  City.Name AS [text()]
        FROM City
        WHERE City.StateID = State.StateID  && City.Population > 100000
        FOR XML Path(")) AS LargeCities
FROM State

Of course, this just gives me the list of city names all run together like this:  “DenverBoulderPuebloColorado Springs”.

I wanted to separate these by commas so I changed the query to “Select City.Name + ‘, ‘ AS [text()], but this gives me an extra comma on the end like this: “Denver, Boulder, Pueblo, Colorado Springs, “.

Of course, I could have used SUBSTRING(“the inner select statement”, 0, LEN(“the inner select statement”) – 2) and been fine, but I didn’t want to rerun the query and it would be harder to ready anyway.

What I ended up doing was inverting the positioning of the “City.Name” and the “, “.  This gave me the following:  “, Denver, Boulder, Pueblo, Colorado Springs”.

Then, all I had to do with the SUBSTRING command was this:  SUBSTRING(“the inner select statement”, 2, 10000).  The “2” as the starting position got me past the first comma, and there was no trailing comma to worry about, since each city was preceded by a comma rather than followed by a comma.  So, the final query looks like this:

SELECT  State.Name,
        SUBSTRING((SELECT ', ' +  City.Name AS [text()]
                   FROM City
                   WHERE City.StateID = State.StateID  && City.Population > 100000
                   FOR XML Path("))), 2, 10000) AS LargeCities
FROM State

and would give the following results:

State Name      LargeCities
Colorado               Denver, Boulder, Pueblo, Colorado Springs

This strategy could be applied to regular code as well when building a comma-separated list.

string list = "";
for (int i = 0; i < items.count; i++)
   list += ", " + items[i].Name;
list = list..Substring(2);