String_agg for SQL Server before 2017

Question

Can anyone help me make this query work for SQL Server 2014?

This is working on PostgreSQL and probably on SQL Server 2017. On Oracle it is listagg instead of string_agg.

Here is the SQL:

select 
    string_agg(t.id,',') AS id
from 
    Table t

I checked on the site some XML option should be used but I could not understand it.

Answer 1

Note that for some characters, the values will be escaped when using FOR XML PATH, for example:

SELECT STUFF((SELECT ',' + V.String
              FROM (VALUES('7 > 5'),('Salt & pepper'),('2
lines'))V(String)
              FOR XML PATH('')),1,1,'');

This returns the string below:

7 > 5,Salt & pepper,2
lines'

This is unlikely desired. You can get around this using TYPE and then getting the value of the XML:

SELECT STUFF((SELECT ',' + V.String
              FROM (VALUES('7 > 5'),('Salt & pepper'),('2
lines'))V(String)
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'');

This returns the string below:

7 > 5,Salt & pepper,2
lines

This would replicate the behaviour of the following:

SELECT STRING_AGG(V.String,',')
FROM VALUES('7 > 5'),('Salt & pepper'),('2
lines'))V(String);

---

Of course, there might be times where you want to group the data, which the above doesn't demonstrate. To achieve this you would need to use a correlated subquery. Take the following sample data:

CREATE TABLE dbo.MyTable (ID int IDENTITY(1,1),
                          GroupID int,
                          SomeCharacter char(1));

INSERT INTO dbo.MyTable (GroupID, SomeCharacter)
VALUES (1,'A'), (1,'B'), (1,'D'),
       (2,'C'), (2,NULL), (2,'Z');

From this the below results are wanted:

GroupID	Characters
1	A,B,D
2	C,Z

To achieve this you would need to do something like this:

SELECT MT.GroupID,
       STUFF((SELECT ',' + sq.SomeCharacter 
              FROM dbo.MyTable sq
              WHERE sq.GroupID = MT.GroupID --This is your correlated join and should be on the same columns as your GROUP BY
                                            --You "JOIN" on the columns that would have been in the PARTITION BY
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'')
FROM dbo.MyTable MT
GROUP BY MT.GroupID; --I use GROUP BY rather than DISTINCT as we are technically aggregating here

--Which would be equivalent to
SELECT MT.GroupID,
       STRING_AGG(MT.SomeCharacter,',')
FROM dbo.MyTable MT
GROUP BY MT.GroupID;

So, if you were grouping on 2 columns, then you would have 2 clauses in your subquery's WHERE: WHERE MT.SomeColumn = sq.SomeColumn AND MT.AnotherColumn = sq.AnotherColumn, and your outer GROUP BY would be GROUP BY MT.SomeColumn, MT.AnotherColumn.

If you the values you want to aggregate are from a different table then you may not need a GROUP BY, such as if you have a one-to-one/many relationship. Something like this would work:

SELECT MT.MyID,
       STUFF((SELECT ',' + YT.SomeString
              FROM dbo.YourTable YT
              WHERE YT.MyID = MT.MyID --Correlated subquery based on Foreign Key
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'')
FROM dbo.MyTable MT;


--Which would be equivalent to
SELECT MT.GroupID,
       STRING_AGG(YT.SomeString,',')
FROM dbo.MyTable MT
     JOIN dbo.YourTable YT ON MT.MyID = YT.MyID 
GROUP BY MT.GroupID;

---

Finally, let's force the order of the aggregated string values by adding an ORDER BY, which you also define in the subquery. For example, assume you wanted to sort the data by the value of the ID descending in the string aggregation:

SELECT MT.GroupID,
       STUFF((SELECT ',' + sq.SomeCharacter 
              FROM dbo.MyTable sq
              WHERE sq.GroupID = MT.GroupID
              ORDER BY sq.ID DESC --This is identical to the ORDER BY you would have in your OVER clause
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'')
FROM dbo.MyTable MT
GROUP BY MT.GroupID;


--Which would be equivalent to
SELECT MT.GroupID,
       STRING_AGG(MT.SomeCharacter,',') WITHIN GROUP (ORDER BY MT.ID DESC)
FROM dbo.MyTable MT
GROUP BY MT.GroupID;

For would produce the following results:

GroupID	Characters
1	D,B,A
2	Z,C

Unsurprisingly, this will never be as efficient as a STRING_AGG, due to having the reference the table multiple times (if you need to perform multiple aggregations, then you need multiple sub queries), but a well indexed table will greatly help the RDBMS. If performance really is a problem, because you're doing multiple string aggregations in a single query, then I would either suggest you need to reconsider if you need the aggregation, or it's about time you considered upgrading.

If you are aggregating non-string data, then you could use CONCAT instead of the concatenation operator (+), as that would implicitly convert the value to a string first. Note, however, that some data types may yield undesired values; for example a datetime CONCATenated to a , might yield the string value 'Jan 16 2020 4:35PM', or a float value might yeild '6.54235e+011'. If you want a specific format, then make use you use CONVERT and an appropriate style code (you may as well need to do further string manipulation as well).

Answer 2

In SQL Server pre-2017, you can do:

select stuff( (select ',' + cast(t.id as varchar(max))
               from tabel t
               for xml path ('')
              ), 1, 1, ''
            );

The only purpose of stuff() is to remove the initial comma. The work is being done by for xml path.