By matt


2012-11-30 04:57:49 8 Comments

I'm finding a way to aggregate strings from different rows into a single row. I'm looking to do this in many different places, so having a function to facilitate this would be nice. I've tried solutions using COALESCE and FOR XML, but they just don't cut it for me.

String aggregation would do something like this:

id | Name                    Result: id | Names
-- - ----                            -- - -----
1  | Matt                            1  | Matt, Rocks
1  | Rocks                           2  | Stylus
2  | Stylus

I've taken a look at CLR-defined aggregate functions as a replacement for COALESCE and FOR XML, but apparently SQL Azure does not support CLR-defined stuff, which is a pain for me because I know being able to use it would solve a whole lot of problems for me.

Is there any possible workaround, or similarly optimal method (which might not be as optimal as CLR, but hey I'll take what I can get) that I can use to aggregate my stuff?

7 comments

@Brian Jorden 2016-12-19 14:10:14

Update: Ms SQL Server 2017+, Azure SQL Database

You can use: STRING_AGG.

Usage is pretty simple for OP's request:

SELECT id, STRING_AGG(name, ', ') AS names
FROM some_table
GROUP BY id

Read More

Well my old non-answer got rightfully deleted (left in-tact below), but if anyone happens to land here in the future, there is good news. They have implimented STRING_AGG() in Azure SQL Database as well. That should provide the exact functionality originally requested in this post with native and built in support. @hrobky mentioned this previously as a SQL Server 2016 feature at the time.

--- Old Post: Not enough reputation here to reply to @hrobky directly, but STRING_AGG looks great, however it is only available in SQL Server 2016 vNext currently. Hopefully it will follow to Azure SQL Datababse soon as well..

@milanio 2017-03-24 17:07:18

I've just tested it and it works like a charm in Azure SQL Database

@a CVn 2017-05-03 14:32:24

STRING_AGG() is stated to become available in SQL Server 2017, in any compatibility level. docs.microsoft.com/en-us/sql/t-sql/functions/…

@Magne 2017-12-06 14:06:47

Yes. STRING_AGG is not available in SQL Server 2016.

@Tom Halladay 2018-05-10 05:44:59

I found Serge's answer to be very promising, but I also encountered performance issues with it as-written. However, when I restructured it to use temporary tables and not include double CTE tables, the performance went from 1 minute 40 seconds to sub-second for 1000 combined records. Here it is for anyone who needs to do this without FOR XML on older versions of SQL Server:

DECLARE @STRUCTURED_VALUES TABLE (
     ID                 INT
    ,VALUE              VARCHAR(MAX) NULL
    ,VALUENUMBER        BIGINT
    ,VALUECOUNT         INT
);

INSERT INTO @STRUCTURED_VALUES
SELECT   ID
        ,VALUE
        ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE) AS VALUENUMBER
        ,COUNT(*) OVER (PARTITION BY ID)    AS VALUECOUNT
FROM    RAW_VALUES_TABLE;

WITH CTE AS (
    SELECT   SV.ID
            ,SV.VALUE
            ,SV.VALUENUMBER
            ,SV.VALUECOUNT
    FROM    @STRUCTURED_VALUES SV
    WHERE   VALUENUMBER = 1

    UNION ALL

    SELECT   SV.ID
            ,CTE.VALUE + ' ' + SV.VALUE AS VALUE
            ,SV.VALUENUMBER
            ,SV.VALUECOUNT
    FROM    @STRUCTURED_VALUES SV
    JOIN    CTE 
        ON  SV.ID = CTE.ID
        AND SV.VALUENUMBER = CTE.VALUENUMBER + 1

)
SELECT   ID
        ,VALUE
FROM    CTE
WHERE   VALUENUMBER = VALUECOUNT
ORDER BY ID
;

@Hrobky 2016-12-13 15:19:50

For those of us who found this and are not using Azure SQL Database:

STRING_AGG() in PostgreSQL, SQL Server 2017 and Azure SQL
https://www.postgresql.org/docs/current/static/functions-aggregate.html
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

GROUP_CONCAT() in MySQL
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

(Thanks to @Brianjorden and @milanio for Azure update)

Example Code:

select Id
, STRING_AGG(Name, ', ') Names 
from Demo
group by Id

SQL Fiddle: http://sqlfiddle.com/#!18/89251/1

@milanio 2017-03-24 17:09:14

I've just tested it and now it works fine with Azure SQL Database.

@Morgan Thrapp 2017-05-02 20:41:36

STRING_AGG got pushed back to 2017. It's not available in 2016.

@Hrobky 2017-05-31 14:00:41

Thank you, Aamir and Morgan Thrapp for SQL Server version change. Updated. (At the time of writing it was claimed to be supported in version 2016.)

@jvc 2018-03-08 15:20:22

You can use += to concatenate strings, for example:

declare @test nvarchar(max)
set @test = ''
select @test += name from names

if you select @test, it will give you all names concatenated

@Hrobky 2018-07-11 11:56:46

Please specify SQL dialect or version since when is it supported.

@Art Schmidt 2019-02-08 21:00:20

This works in SQL Server 2012. Note that a comma-separated list can be created with select @test += name + ', ' from names

@Serge Belov 2012-12-03 10:50:07

SOLUTION

The definition of optimal can vary, but here's how to concatenate strings from different rows using regular Transact SQL, which should work fine in Azure.

;WITH Partitioned AS
(
    SELECT 
        ID,
        Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
        COUNT(*) OVER (PARTITION BY ID) AS NameCount
    FROM dbo.SourceTable
),
Concatenated AS
(
    SELECT 
        ID, 
        CAST(Name AS nvarchar) AS FullName, 
        Name, 
        NameNumber, 
        NameCount 
    FROM Partitioned 
    WHERE NameNumber = 1

    UNION ALL

    SELECT 
        P.ID, 
        CAST(C.FullName + ', ' + P.Name AS nvarchar), 
        P.Name, 
        P.NameNumber, 
        P.NameCount
    FROM Partitioned AS P
        INNER JOIN Concatenated AS C 
                ON P.ID = C.ID 
                AND P.NameNumber = C.NameNumber + 1
)
SELECT 
    ID,
    FullName
FROM Concatenated
WHERE NameNumber = NameCount

EXPLANATION

The approach boils down to three steps:

  1. Number the rows using OVER and PARTITION grouping and ordering them as needed for the concatenation. The result is Partitioned CTE. We keep counts of rows in each partition to filter the results later.

  2. Using recursive CTE (Concatenated) iterate through the row numbers (NameNumber column) adding Name values to FullName column.

  3. Filter out all results but the ones with the highest NameNumber.

Please keep in mind that in order to make this query predictable one has to define both grouping (for example, in your scenario rows with the same ID are concatenated) and sorting (I assumed that you simply sort the string alphabetically before concatenation).

I've quickly tested the solution on SQL Server 2012 with the following data:

INSERT dbo.SourceTable (ID, Name)
VALUES 
(1, 'Matt'),
(1, 'Rocks'),
(2, 'Stylus'),
(3, 'Foo'),
(3, 'Bar'),
(3, 'Baz')

The query result:

ID          FullName
----------- ------------------------------
2           Stylus
3           Bar, Baz, Foo
1           Matt, Rocks

@QMaster 2014-02-19 09:04:08

I checked the time consumption of this way against xmlpath and i reached about 4 milliseconds vs about 54 milliseconds. so the xmplath way is better specially in large cases. I'll write the compare code in an separate answer.

@Romano Zumbé 2014-10-15 11:35:53

It is far better since this approach only works for 100 values maximum.

@Serge Belov 2014-10-15 22:31:19

@romano-zumbé Use MAXRECURSION to set the CTE limit to whatever you need.

@Nickolay 2016-11-28 14:05:43

Surprisingly, CTE was way slower for me. sqlperformance.com/2014/08/t-sql-queries/… compares a bunch of techniques, and seems to agree with my results.

@Ardalan Shahgholi 2016-12-09 16:31:47

This solution for a table with more than 1 million record doesn't work. Also, we have a limit on recursive depth

@GoldBishop 2017-05-04 18:06:19

@ArdalanShahgholi you may want to limit your Range of data to be operated on. Otherwise, you will need to use an XML form for the concatenation. Recursive CTE's are layered evaluations, so a 1M record action would result in roughly 2M records as a result, before you do the final limitation.

@nurettin 2017-08-24 06:16:50

mssql in azure now has string_agg

@Tom 2018-05-01 20:19:38

While I'm awestruck that you (or anyone) could come up with this solution, the XML version (first by "slachterman" and them much more read-ably by "QMaster" below) still (barring having SS 2017 and therefore String_Agg), IMHO, beats it hands down: 1) It's ~40X faster (for me SQL Server 2008 R2 on Windows Server 2008 R2 on Intel Xeon E5-2630 v4 @2.20 GHZ x2 w/ ~1 GB free w/ my sample), 2) (Perhaps more importantly) It avoids 2 extra copies of almost the same code (in the sub-Selects).

@QMaster 2014-02-19 09:14:48

Although @serge answer is correct but i compared time consumption of his way against xmlpath and i found the xmlpath is so faster. I'll write the compare code and you can check it by yourself. This is @serge way:

DECLARE @startTime datetime2;
DECLARE @endTime datetime2;
DECLARE @counter INT;
SET @counter = 1;

set nocount on;

declare @YourTable table (ID int, Name nvarchar(50))

WHILE @counter < 1000
BEGIN
    insert into @YourTable VALUES (ROUND(@counter/10,0), CONVERT(NVARCHAR(50), @counter) + 'CC')
    SET @counter = @counter + 1;
END

SET @startTime = GETDATE()

;WITH Partitioned AS
(
    SELECT 
        ID,
        Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
        COUNT(*) OVER (PARTITION BY ID) AS NameCount
    FROM @YourTable
),
Concatenated AS
(
    SELECT ID, CAST(Name AS nvarchar) AS FullName, Name, NameNumber, NameCount FROM Partitioned WHERE NameNumber = 1

    UNION ALL

    SELECT 
        P.ID, CAST(C.FullName + ', ' + P.Name AS nvarchar), P.Name, P.NameNumber, P.NameCount
    FROM Partitioned AS P
        INNER JOIN Concatenated AS C ON P.ID = C.ID AND P.NameNumber = C.NameNumber + 1
)
SELECT 
    ID,
    FullName
FROM Concatenated
WHERE NameNumber = NameCount

SET @endTime = GETDATE();

SELECT DATEDIFF(millisecond,@startTime, @endTime)
--Take about 54 milliseconds

And this is xmlpath way:

DECLARE @startTime datetime2;
DECLARE @endTime datetime2;
DECLARE @counter INT;
SET @counter = 1;

set nocount on;

declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))

WHILE @counter < 1000
BEGIN
    insert into @YourTable VALUES (@counter, ROUND(@counter/10,0), CONVERT(NVARCHAR(50), @counter) + 'CC')
    SET @counter = @counter + 1;
END

SET @startTime = GETDATE();

set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

SET @endTime = GETDATE();

SELECT DATEDIFF(millisecond,@startTime, @endTime)
--Take about 4 milliseconds

@Tom 2018-05-01 19:55:10

+1, you QMaster (of the Dark Arts) you! I got an even more dramatic diff. (~3000 msec CTE vs. ~70 msec XML on SQL Server 2008 R2 on Windows Server 2008 R2 on Intel Xeon E5-2630 v4 @2.20 GHZ x2 w/ ~1 GB free). Only suggestions are: 1) Either use OP's or (preferably) generic terms for both versions, 2) Since OP's Q. is how to "concatenate/aggregate strings" and this is only needed for strings (vs. a numeric value), generic terms are too generic. Just use "GroupNumber" and "StringValue", 3) Declare and use a "Delimiter" Variable and use "Len(Delimiter)" vs. "2".

@slachterman 2012-12-08 22:59:32

Are methods using FOR XML PATH like below really that slow? Itzik Ben-Gan writes that this method has good performance in his T-SQL Querying book (Mr. Ben-Gan is a trustworthy source, in my view).

create table #t (id int, name varchar(20))

insert into #t
values (1, 'Matt'), (1, 'Rocks'), (2, 'Stylus')

select  id
        ,Names = stuff((select ', ' + name as [text()]
        from #t xt
        where xt.id = t.id
        for xml path('')), 1, 2, '')
from #t t
group by id

@milivojeviCH 2012-12-09 15:15:10

Don't forget to put an index on that id column once the size of a table becomes a problem.

@Nickolay 2016-11-28 14:09:56

And after reading how stuff/for xml path work (stackoverflow.com/a/31212160/1026), I'm confident that it's a good solution despite XML in its name :)

@GoldBishop 2017-05-04 18:09:00

@slackterman Depends on the number of records to be operated on. I think XML is deficient at the low counts, compared to CTE, but at the upper volume counts, alleviates the Recursion Dept limitation and is easier to navigate, if done correctly and succinctly.

@devinbost 2017-05-09 05:46:29

FOR XML PATH methods blow up if you have emojis or special / surrogate characters in your data!!!

@Frédéric 2019-05-03 21:20:30

This code results in xml-encoded text (& switched to &amp;, and so on). A more correct for xmlsolution is provided here.

Related Questions

Sponsored Content

45 Answered Questions

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

16 Answered Questions

17 Answered Questions

[SOLVED] What is the best way to paginate results in SQL Server

13 Answered Questions

[SOLVED] Best way to get identity of inserted row?

  • 2008-09-03 21:32:02
  • Oded
  • 760002 View
  • 1014 Score
  • 13 Answer
  • Tags:   sql sql-server tsql

9 Answered Questions

[SOLVED] How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

0 Answered Questions

String aggregate function for natively compiled stored procedure in Sql Server 2016

  • 2017-03-10 12:30:32
  • sql_xc
  • 228 View
  • 1 Score
  • 0 Answer
  • Tags:   sql-server

0 Answered Questions

how to get SQL to process an aggregate in a specific order

  • 2014-11-04 21:34:46
  • David Orr
  • 54 View
  • 0 Score
  • 0 Answer
  • Tags:   sql sql-server

3 Answered Questions

1 Answered Questions

[SOLVED] Using sys.fn_IsBitSetInBitmask in SQL Azure

  • 2013-11-19 22:48:13
  • David Wengier
  • 261 View
  • 2 Score
  • 1 Answer
  • Tags:   sql sql-server azure

Sponsored Content