ListAGG in SQLSERVER

Question

I'm trying to aggregate a 'STRING' field in SQLServer. I would like to find the same function LISTAGG like in Oracle .

Do you know how to do the same function or an another method?

For Example,

Field A | Field B
1       |  A
1       |  B
2       |  A

And I would like that the result of this query will be

1 | AB
2 | A

Please add your vote for DISTINCT: https://feedback.azure.com/forums/908035-sql-server/suggestions/35243533-support-distinct-for-string-agg — ValGe, Dec 05 '18 at 07:59

score 193 · Answer 1 · edited Apr 15 '18 at 21:42

193

MySQL

SELECT FieldA
     , GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ',') AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

Oracle & DB2

SELECT FieldA
     , LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

PostgreSQL

SELECT FieldA
     , STRING_AGG(FieldB, ',' ORDER BY FieldB) AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

SQL Server

SQL Server ≥ 2017 & Azure SQL

SELECT FieldA
     , STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

SQL Server ≤ 2016 (CTE included to encourage the DRY principle)

  WITH CTE_TableName AS (
       SELECT FieldA, FieldB
         FROM TableName)
SELECT t0.FieldA
     , STUFF((
       SELECT ',' + t1.FieldB
         FROM CTE_TableName t1
        WHERE t1.FieldA = t0.FieldA
        ORDER BY t1.FieldB
          FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
  FROM CTE_TableName t0
 GROUP BY t0.FieldA
 ORDER BY FieldA;

SQLite

Ordering requires a CTE or subquery

  WITH CTE_TableName AS (
       SELECT FieldA, FieldB
         FROM TableName
        ORDER BY FieldA, FieldB)
SELECT FieldA
     , GROUP_CONCAT(FieldB, ',') AS FieldBs
  FROM CTE_TableName
 GROUP BY FieldA
 ORDER BY FieldA;

Without ordering

SELECT FieldA
     , GROUP_CONCAT(FieldB, ',') AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

edited Apr 15 '18 at 21:42

Peter K

77
2
6

answered Oct 13 '13 at 18:27

Manas Kumar

2,411
3
16
23

7

great answer - a bit more concise than the marked answer + also mentions other sql dialects. The sqlserver syntax for this is really unintuitive. I was also surprised that the extra brackets around the select in stuff are necessary. – JonnyRaa Apr 02 '14 at 09:07
@manas_kumar but... why you use STUFF? it is unnecessary. Am I missing something? – Ricardo C May 18 '16 at 18:12
@Ricardo_C STUFF is being used here to remove the leading comma. – DigitalDan Jun 01 '16 at 06:31
1

To be complete, this needs to be added to the sql server example: http://stackoverflow.com/a/8856789 It prevents certain characters being replaced by XML tags. – Wouter Sep 20 '16 at 10:05
This is great!!! ... but for some reason the `TableName` in the inner `SELECT` cannot be a CTE from the outer select. Rrgggh. – Dan Lenski Sep 23 '16 at 15:50
1

@DanLenski That doesn't appear to be the case, as I am referencing a `cte` successfully on my other monitor right now, I would imagine you were not constructing your query correctly? – iamdave Mar 30 '17 at 12:03
I wish if there was a version for sybase aser database though ill try to create one – Baalback Aug 10 '23 at 08:27

Taryn · Accepted Answer · 2019-12-12T16:11:30.683

99

Starting in SQL Server 2017 the STRING_AGG function is available which simplifies the logic considerably:

select FieldA, string_agg(FieldB, '') as data
from yourtable
group by FieldA

See SQL Fiddle with Demo

In SQL Server you can use FOR XML PATH to get the result:

select distinct t1.FieldA,
  STUFF((SELECT distinct '' + t2.FieldB
         from yourtable t2
         where t1.FieldA = t2.FieldA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'') data
from yourtable t1;

See SQL Fiddle with Demo

edited Dec 12 '19 at 16:11

answered Mar 18 '13 at 13:18

Taryn

242,637
56
362
405

i would like to insert an order by clause in the sub-select. Do you know how insert this clause? – user1557642 Mar 18 '13 at 13:38
@user1557642 What do you want to order? Do you want the values reversed? You can easily add an `ORDER BY` see this demo -- http://www.sqlfiddle.com/#!3/0836c/7. In this case the result for `FieldA` will show `BA` – Taryn Mar 18 '13 at 13:39
in fact i have 10 lines to aggregate. But SQLServer do a alphabatical rank before the aggragation. I would like to aggregate with my own parameters(a third field) and not the alphabatical order. – user1557642 Mar 18 '13 at 13:41
3

@user1557642 You can use an `ORDER BY`, you will just have to include a `GROUP BY` in the subquery, see this demo -- http://www.sqlfiddle.com/#!3/0836c/7 – Taryn Mar 18 '13 at 13:42
breaks for strings with special characters like '<'; they get XML encoded. Not a solution at all. Total hack, based on obsolete, undocumented behavior of 'for xml path'. – Triynko Dec 02 '16 at 21:04
I don't much agree with the edit of typoerrpr, that's basically adding a new answer to an existing one. What you say Bluefeet? – TT. Oct 20 '17 at 09:34
@typoerrpr Feel free to add that as another answer to the question. – Taryn Oct 20 '17 at 11:50
I had a use case where I needed pipe as the delimitter and didn't want the pipe in the beginning. To modify the above sample query to make this happen, I did: select distinct t1.FieldA, STUFF((SELECT distinct '|' + t2.FieldB from yourtable t2 where t1.FieldA = t2.FieldA FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') data from yourtable t1; – thetallone Aug 15 '18 at 19:58
@Taryn I must disagree; I just checked the editlog for this answer and --as he described in it -- it just gives a more generic version (which makes it easier to understand/modify) of the provided snippet. If coming across that answer "twice" the viewer would likely assume it's a duplicate. – Jonas D. Oct 25 '18 at 13:23
1

This answer is no longer good. Current versions of SQL server have STRING_AGG which does the same job and has always been faster in my tests. – Qwertie Nov 26 '19 at 23:58
1

@Qwertie The answer still works, but I have updated the answer to include the STRING_AGG function version as well. – Taryn Dec 12 '19 at 16:12

vldmrrdjcc · Answer 3 · 2017-08-25T14:55:21.070

22

In SQL Server 2017 STRING_AGG is added:

SELECT t.name,STRING_AGG (c.name, ',') AS csv
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
GROUP BY t.name
ORDER BY 1

Also, STRING_SPLIT is usefull for the opposite case and available in SQL Server 2016

edited Aug 25 '17 at 14:55

answered Jan 05 '17 at 15:30

vldmrrdjcc

2,082
5
22
41

2

STRING_AGG / STRING_SPLIT were also recently added to Azure SQL Database as well for anyone that happens to land here on that platform. – Brian Jorden Mar 23 '17 at 04:22
4

Note: vNext has been named SQL Server 2017 – Charles Burns Aug 23 '17 at 21:27
Do you know how to use distinct c.name, so all concatenated names are unique? – Sergey Malyutin Feb 21 '18 at 13:16

score 2 · Answer 4 · edited Nov 19 '22 at 04:49

This might be useful to someone also, i.e. for data analysis and data profiling type of purposes. (i.e. not grouped by).

Prior to the SQL Server 2017 STRING_AGG function existing ..

(i.e. returns just one row ..)

SELECT DISTINCT
    SUBSTRING(
        STUFF((
            SELECT DISTINCT ',' + [FieldB] 
            FROM tablename 
            ORDER BY 1 
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
        , 1, 0, '')
    , 2, 9999)
FROM tablename

It returns comma separated values - e.g. "A,B".

ListAGG in SQLSERVER

4 Answers4

MySQL

Oracle & DB2

PostgreSQL

SQL Server

SQLite

Linked

Related