How to return a comma separated values of column without having to loop through the result set

Question

Let say I have this 2 table

+----+---------+       +----+-----------+----------------+
| Id | Country |       | Id | CountryId | City           |
+----+---------+       +----+-----------+----------------+
| 1  | USA     |       | 1  |   1       | Washington, DC |
+----+---------+       +----+-----------+----------------+
| 2  | Canada  |       | 2  |   2       | Ottawa         |
+----+---------+       +----+-----------+----------------+
                       | 3  |   1       | New York       |
                       +----+-----------+----------------+
                       | 4  |   1       | Baltimore      |
                       +----+-----------+----------------+

I need to produce a result like:

Id | Country | Cities
---+---------+--------------------------------------
1  | USA     | Washington, DC, New York, Baltimore
---+------------------------------------------------
2  | Canada  | Ottawa

So far, I am looping through the left side table result like this:

DECLARE @table 
(
   Id INT IDENTITY(1, 1),
   CountryId INT,
   City VARCHAR(50)
 )

DECLARE @tableString
(
   Id INT IDENTITY(1, 1),
   CountryId INT,
   Cities VARCHAR(100)
)

INSERT INTO @table
SELECT Id, City
FROM tblCountries

DECLARE @city VARCHAR(50)
DECLARE @id INT
DECLARE @count INT
DECLARE @i INT = 1
SELECT @count = COUNT(*) FROM @table

WHILE (@i <= @count)
  BEGIN
    SELECT @Id = Id, @city = City FROM @table WHERE Id = @i
    IF(EXISTS(SELECT * FROM @tableString WHERE CountryId = @Id))  
      BEGIN
         UPDATE @tableString SET Cities = Cities + ', ' + @city WHERE Id = @Id
      END
    ELSE
      BEGIN
        INSERT INTO @tableString (CountryId, city) VALUES (@Id, @city)
      END
    SET @i = @i + 1
  END

  SELECT tc.Id, tc.Country, ts.Cities
  FROM tblCountries tc
  LEFT JOIN @tableString ts
  ON tc.Id = ts.CountryId

My concern is that with all those looping in TSQL, it may be a performance killer. Even with fewer, it appears to be slow. Is there a better way to concatenate those string without having to loop through the data set as if I was working in C# . Thanks for helping

See if these help: https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv — Jeremy J., Sep 28 '18 at 17:59
@JeremyJ., am I missing something? It seems like this is related to XML — Richard77, Sep 28 '18 at 18:33
Possible duplicate of [get a comma delimited string from rows](https://stackoverflow.com/questions/41787834/get-a-comma-delimited-string-from-rows) — Zohar Peled, Sep 28 '18 at 18:35
It seems strange on the surface, but using the FOR XML clause a common way to build a comma separated list in a select statement SQL Server. — Jeremy J., Sep 28 '18 at 18:59
What version of SQL Server? If you are using 2017 you should look into the [string aggregate](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017) function(s). — gomory-chvatal, Sep 29 '18 at 16:20

score 1 · Answer 1 · answered Oct 01 '18 at 06:12

This was answered many times, but I've got the feeling, that some explanation might help you...

... am I missing something? It seems like this is related to XML

The needed functionality STRING_AGG() was introduced with SQL-Server 2017. The other direction STRING_SPLIT() came with v2016.

But many people still use older versions (and will do this for years), so we need workarounds. There were approaches with loops, bad and slow... And you might use recursive CTEs. And - that's the point here! - we can use some abilities of XML to solve this.

Try this out:

DECLARE @xml XML=
N'<root>
    <element>text1</element>
    <element>text2</element>
    <element>text3</element>
</root>';

--The query will return the first <element> below <root> and return text1.

SELECT @xml.value(N'(/root/element)[1]','nvarchar(max)');

--But now try this:

SELECT @xml.value(N'(/root)[1]','nvarchar(max)')

The result is text1text2text3.
The reason for this: If you call .value() on an element without a detailed specification of what you want to read, you'll get the whole element back. Find details here.

Now imagine an XML like this

DECLARE @xml2 XML=
N'<root>
    <element>, text1</element>
    <element>, text2</element>
    <element>, text3</element>
</root>';

With the same query as above you'd get , text1, text2, text3. The only thing left is to cut off the leading comma and the space. This is done - in most examples - with STUFF().

So the challenge is to create this XML. And this is what you find in the linked examples.

A general example is this: Read all tables and list their columns as a CSV-list:

SELECT TOP 10
       TABLE_NAME
      ,STUFF(
       (SELECT ',' + c.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c 
        WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
        ORDER BY c.COLUMN_NAME
        FOR XML PATH('')
       ),1,1,'') AS AllTableColumns
FROM INFORMATION_SCHEMA.TABLES t

How to return a comma separated values of column without having to loop through the result set

1 Answers1