Optimal Way to Concatenate/Aggregate Strings

Optimal way to concatenate/aggregate strings

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

Does T-SQL have an aggregate function to concatenate strings?

for SQL Server 2017 and up use:

STRING_AGG()

set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
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

SELECT
    HeaderValue, STRING_AGG(ChildValue,', ')
    FROM @YourTable
    GROUP BY HeaderValue

OUTPUT:

HeaderValue 
----------- -------------
1           CCC
2           B<&>B, AAA
3           <br>, A & Z

(3 rows affected)

for SQL Server 2005 and up to 2016, you need to do something like this:

--Concatenation with FOR XML and eleminating control/encoded character expansion "& < >"
set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
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

OUTPUT:

HeaderValue ChildValues
----------- -------------------
1           CCC
2           AAA, B<&>B
3           <br>, A & Z

(3 row(s) affected)

Also, watch out, not all FOR XML PATH concatenations will properly handle XML special characters like my above example will.

How to aggregate and string concatenate at same time in Oracle?

Would this do?

SQL> with test (customer, product, amount) as
  2    (select 'a', 'table', 500 from dual union all
  3     select 'a', 'table', 300 from dual union all
  4     select 'a', 'chair', 100 from dual union all
  5     select 'b', 'rug'  , 50  from dual union all
  6     select 'b', 'chair', 200 from dual
  7    )
  8  select customer,
  9    listagg (product, ', ') within group (order by null) product,
 10    sum(sum_amount) amount
 11  from (select customer, product, sum(amount) sum_amount
 12        from test
 13        group by customer, product
 14       )
 15  group by customer
 16  order by customer;

C PRODUCT                  AMOUNT
- -------------------- ----------
a chair, table                900
b chair, rug                  250

SQL>

How to concatenate group members into a resulting column of an SQL query

use String_agg as follows

SELECT groupid,
       groupname,
       String_agg(groupmembername, ',') AS GroupMemberName
FROM   lefttable1 t1
       JOIN righttable2 t2
         ON t1.groupid = t2.groupid
GROUP  BY groupid,
          groupname  

or as follows

SELECT groupid,
       groupname,
       String_agg(groupmembername, ',') AS GroupMemberName
FROM   (SELECT groupid,
               groupname,
               groupmembername
        FROM   lefttable1 t1
               JOIN righttable2 t2
                 ON t1.groupid = t2.groupid) T3  

Concatenate/aggregate strings with JSON in SQL Server

You cannot replace the XML approach with JSON. This string concatenation works due to some XML inner peculiarities, which are not the same in JSON.

Starting with SQL Server 2017 onwards you can use STRING_AGG(), but with earlier versions, the XML approach is the way to go.

Some background and a hint

First the hint: The code you showed is not safe for the XML special characters. Check my example below.

First I declare a simple XML

DECLARE  @xml XML=
N'<a>
  <b>1</b>
  <b>2</b>
  <b>3</b>
  <c>
    <d>x</d>
    <d>y</d>
    <d>z</d>
  </c>
</a>';

--The XPath . tells the XML engine to use the current node (and all within)

--Therefore this will return any content within the XML

SELECT @xml.value('.','varchar(100)')

--You can specify the path to get 123 or xyz

SELECT @xml.query('/a/b').value('.','varchar(100)')
SELECT @xml.query('//d').value('.','varchar(100)')
    

Now your issue to concatenate tabular data:

DECLARE @tbl TABLE(SomeString VARCHAR(100));
INSERT INTO @tbl VALUES('This'),('will'),('concatenate'),('magically'),('Forbidden Characters & > <');

--The simple FOR XML query will tag the column with <SomeString> and each row with <row>:

SELECT SomeString FROM @tbl FOR XML PATH('row');

--But we can create the same without any tags:

--Attention: Look closely, that the result - even without tags - is XML typed and looks like a hyper link in SSMS.

SELECT SomeString AS [*] FROM @tbl FOR XML PATH('');

--Now we can use as a sub-select within a surrounding query.

--The result is returned as string, not XML typed anymore... Look at the forbidden chars!

SELECT
 (SELECT SomeString FROM @tbl FOR XML PATH('row'))
,(SELECT SomeString AS [*] FROM @tbl FOR XML PATH(''))

--We can use ,TYPE to enforce the sub-select to be treated as XML typed itself

--This allows to use .query() and/or .value()

SELECT
 (SELECT SomeString FROM @tbl FOR XML PATH('row'),TYPE).query('data(//SomeString)').value('.','nvarchar(max)')
,(SELECT SomeString AS [*] FROM @tbl FOR XML PATH(''),TYPE).value('.','nvarchar(max)')

XQuery's .data() can be used to concatenate named elements with blanks in between.

XQuery's .value() must be used to re-escpae forbidden characters.

Aggregate function to concatenate strings joining two tables?

You should be able to alter the question to the following to get the final result:

SELECT distinct t1.RowID,
STUFF(
           (SELECT ', ' + cast(t2.Field2Show as varchar(10))
            FROM @SecondTable t2
            INNER JOIN @FirstTable t  -- join on @FirstTable based on LinkedField
              on t.LinkedField = t2.LinkedField
            WHERE t1.RowID = t.RowID -- associate the RowId from FirstTable to concatenate
            ORDER BY t2.Field2Show
            FOR XML PATH(''), TYPE).value('.','varchar(max)') ,1,2, '') AS ChildValues
FROM @FirstTable t1;

See SQL Fiddle with Demo

How to efficiently concatenate strings in go

New Way:

From Go 1.10 there is a strings.Builder type, please take a look at this answer for more detail.

Old Way:

Use the bytes package. It has a Buffer type which implements io.Writer.

package main

import (
    "bytes"
    "fmt"
)

func main() {
    var buffer bytes.Buffer

    for i := 0; i < 1000; i++ {
        buffer.WriteString("a")
    }

    fmt.Println(buffer.String())
}

This does it in O(n) time.

---

Related Topics