Concat groups in SQL Server

Question

If I have a table like this:

+------------+
| Id | Value |
+------------+
| 1  | 'A'   |
|------------|
| 1  | 'B'   |
|------------|
| 2  | 'C'   |
+------------+

How can I get a resultset like this:

+------------+
| Id | Value |
+------------+
| 1  | 'AB'  |
|------------|
| 2  | 'C'   |
+------------+

I know this is really easy to do in MySQL using GROUP_CONCAT, but I need to be able to do it in MSSQL 2005

Thanks

(Duplicate of How to use GROUP BY to concatenate strings in SQL Server?)

score 11 · Accepted Answer · edited Jun 21 '15 at 01:42

11

For a clean and efficient solution you can create an user defined aggregate function, there is even an example that does just what you need.
You can then use it like any other aggregate function (with a standard query plan):

query plan

edited Jun 21 '15 at 01:42

Spooky

2,966
8
27
41

answered Dec 17 '09 at 15:31

Pent Ploompuu

5,364
1
27
47

the most clean solution yet – Spyros Sep 02 '14 at 08:47

score 5 · Answer 2 · answered Jun 02 '09 at 18:29

5

This will do:

SELECT mt.ID,
       SUBSTRING((SELECT mt2.Value
                  FROM   MyTable AS mt2
                  WHERE  mt2.ID = mt.ID
                  ORDER BY mt2.VALUE
                  FOR XML PATH('')), 3, 2000) AS JoinedValue
FROM   MyTable AS mt

answered Jun 02 '09 at 18:29

Aaron Alton

22,728
6
34
32

This concats the values in a XML document, which is undesirable. – Boog Sep 22 '09 at 20:48
4

No, it uses XML functions. There are no XML documents involved. Why is it "undesirable"? – Aaron Alton Sep 24 '09 at 00:40

score 4 · Answer 3 · answered Jun 02 '09 at 18:27

4

See:

http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html

answered Jun 02 '09 at 18:27

vladr

65,483
18
129
130

Please find my blog covering scenarios for grouped concatenation in SQL server. https://blog.vcillusion.co.in/understanding-the-grouped-concatenation-sql-server/ – vCillusion Jun 02 '18 at 22:25
Without using Value with XML-PATH might lead to parsing errors for XML encoded values. – vCillusion Jun 02 '18 at 22:26

score 3 · Answer 4 · edited May 23 '17 at 12:18

3

Often asked here.

The most efficient way is using the FOR XML PATH trick.

edited May 23 '17 at 12:18

Community

1
1

answered Jun 02 '09 at 18:28

Cade Roux

88,164
40
182
265

Also, we have STRING_AGG Introduced in SQL Server (starting with 2017), it concatenates the values of string expressions and places separator values between them (not added at the end of the string). https://blog.vcillusion.co.in/understanding-the-grouped-concatenation-sql-server/ – vCillusion Jun 02 '18 at 22:29

score 1 · Answer 5 · answered Jun 02 '09 at 18:49

This just came to me as one possible solution. I have no idea as to performance, but I thought it would be an interesting way to solve the problem. I tested that it works in a simple situation (I didn't code to account for NULLs). Feel free to give it a test to see if it performs well for you.

The table that I used included an id (my_id). That could really be any column that is unique within the group (grp_id), so it could be a date column or whatever.

;WITH CTE AS (
    SELECT
        T1.my_id,
        T1.grp_id,
        CAST(T1.my_str AS VARCHAR) AS my_str
    FROM
        dbo.Test_Group_Concat T1
    WHERE NOT EXISTS (SELECT * FROM dbo.Test_Group_Concat T2 WHERE T2.grp_id = T1.grp_id AND T2.my_id < T1.my_id)
    UNION ALL
    SELECT
        T3.my_id,
        T3.grp_id,
        CAST(CTE.my_str + T3.my_str AS VARCHAR)
    FROM
        CTE
    INNER JOIN dbo.Test_Group_Concat T3 ON
        T3.grp_id = CTE.grp_id AND
        T3.my_id > CTE.my_id
    WHERE
        NOT EXISTS (SELECT * FROM dbo.Test_Group_Concat T4 WHERE
        T4.grp_id = CTE.grp_id AND
        T4.my_id > CTE.my_id AND
        T4.my_id < T3.my_id)
)
SELECT
    CTE.grp_id,
    CTE.my_str
FROM
    CTE
INNER JOIN (SELECT grp_id, MAX(my_id) AS my_id FROM CTE GROUP BY grp_id) SQ ON
    SQ.grp_id = CTE.grp_id AND
    SQ.my_id = CTE.my_id
ORDER BY
    CTE.grp_id

Concat groups in SQL Server

5 Answers5

Linked

Related