ListAGG in SQLSERVER

Problem

In SQLServer, I’m attempting to aggregate a ‘STRING’ field. I’m looking for a function similar to Oracle’s LISTAGG.

Do you know how to perform the same function in a different way?

For Example,

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

And I’d like the query’s result to be as follows:

1 | AB
2 | A

Asked by user1557642

Solution #1

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

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

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

SQL Server 2017 and Azure SQL Database

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

SQL Server 2016 is the latest version of SQL Server (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;

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;

Answered by Manas Kumar

Solution #2

The STRING AGG function is included in SQL Server 2017 and simplifies the logic significantly:

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

Demonstration of SQL Fiddle

To get the following result in SQL Server, use FOR XML PATH:

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;

Demonstration of SQL Fiddle

Answered by Taryn

Solution #3

STRING AGG is a new feature in SQL Server 2017:

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 available in SQL Server 2016 is STRING SPLIT, which is beneficial in the opposite circumstance.

Answered by vldmrrdjcc

Solution #4

This could be valuable to someone else as well…

For the purposes of a data analyst and data profiling, for example (i.e. not grouped by)

Prior to the introduction of the SQL*Server 2017 String agg function…

(That is, it only returns 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 

For example, yields the comma-separated values A,B.

Answered by Allan F

Post is based on https://stackoverflow.com/questions/15477743/listagg-in-sqlserver