Grouped String Aggregation/Listagg for SQL Server

Grouped string aggregation / LISTAGG for SQL Server

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

It is an interesting problem in Transact SQL, for which there are a number of solutions and considerable debate. How do you go about producing a summary result in which a distinguishing column from each row in each particular category is listed in a 'aggregate' column? A simple, and intuitive way of displaying data is surprisingly difficult to achieve. Anith Sen gives a summary of different ways, and offers words of caution over the one you choose...

ListAGG in SQLSERVER

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

SQL string aggregation based on a group by

Sybase supports the LIST() function. So:

select chrs, count(*) as cnt
from (select t.number, list(t.chr, '' order by t.chr) as chrs
      from t
      group by t.number
     ) n
group by chrs
order by count(*) desc;

How to use GROUP BY to concatenate strings in SQL Server?

No CURSOR, WHILE loop, or User-Defined Function needed.

Just need to be creative with FOR XML and PATH.

[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

SQL Server - LIST AGG with quotes

Just add '''' /'"'to XML + STUFF method:

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;

Rextester Demo

EDIT:

Or use QUOTENAME:

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

Rextester Demo2

LISTAGG function: result of string concatenation is too long

Since the aggregates string can be longer than 4000 bytes, you can't use the LISTAGG function. You could potentially create a user-defined aggregate function that returns a CLOB rather than a VARCHAR2. There is an example of a user-defined aggregate that returns a CLOB in the original askTom discussion that Tim links to from that first discussion.

SQL query to listAgg and group by

with table_1 (colA) as (
        select '1000016932,1000020056,1000020100,1000020144,1000020243' from dual
     ),
     prep (lvl, token) as (
        select level, regexp_substr(colA, '[^,]+', 1, level) from table_1
        connect by level <= regexp_count(colA, ',') + 1
        and colA = prior colA
        and prior sys_guid() is not null
     )
select p1.token as token_1, p2.token as token_2 
from prep p1 join prep p2 on p1.lvl < p2.lvl;

This assumes there are no nulls between commas (you don't have two consecutive commas with nothing between them, marking a "null" in the sequence).

Result:

TOKEN_1    TOKEN_2
---------- ----------
1000016932 1000020056
1000016932 1000020100
1000016932 1000020144
1000016932 1000020243
1000020056 1000020100
1000020056 1000020144
1000020056 1000020243
1000020100 1000020144
1000020100 1000020243
1000020144 1000020243

To allow several rows in the input table (assuming there is a row_id column of some sort in the initial table):

with table_1 (row_id, colA) as (
        select 101, '1000016932,1000020056,1000020100,1000020144,1000020243' from dual union all
        select 102, '1000040042,1000045543,1000045664'                       from dual
     ),
     prep (lvl, row_id, token) as (
        select level, row_id, regexp_substr(colA, '[^,]+', 1, level) from table_1
        connect by level <= regexp_count(colA, ',') + 1
        and row_id = prior row_id
        and prior sys_guid() is not null
     )
select p1.row_id, p1.token as token_1, p2.token as token_2 
from prep p1 join prep p2 on p1.row_id = p2.row_id and p1.lvl < p2.lvl
order by row_id, token_1;

Result:

    ROW_ID TOKEN_1    TOKEN_2
---------- ---------- ----------
       101 1000016932 1000020144
       101 1000016932 1000020056
       101 1000016932 1000020100
       101 1000016932 1000020243
       101 1000020056 1000020243
       101 1000020056 1000020100
       101 1000020056 1000020144
       101 1000020100 1000020243
       101 1000020100 1000020144
       101 1000020144 1000020243
       102 1000040042 1000045543
       102 1000040042 1000045664
       102 1000045543 1000045664

---

Related Topics