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
1

4 Answers

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;
6

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

11

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

3

This might be useful to someone also ..

i.e. For a data analyst and data profiling type of purposes ..(i.e. not grouped by) ..

Prior to the SQL*Server 2017 String_agg function existence ..

(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

e.g. returns comma separated values A,B