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 

Answer 1

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; 

Answer 2

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