SQL Cursor merge fields

Question

Following Setup:
https://i.stack.imgur.com/lHM9H.png
(As soon as you earn 10 reputation on the site, you'll be able to embed images)

I'd like to add up all the same numbers in the second column, but i don't get it how to do so. Output should look like the following:

+------------+------------+
| 00000030   | 106        |
| 00000001   | 106,107,125|
| 00000002   | 103        |
| 00000002   | 103        |
 and so on ....
+------------+------------+

Is that even possible with simple SQL or do i have to use an sql cursor?

Regardless of rep count, you can always post data as formatted text, which is the preferred method to post data. — HoneyBadger, Feb 01 '18 at 14:30
The keyword you are looking for in the docs is "aggregate function" :) — giorgiga, Feb 01 '18 at 14:33

score 0 · Accepted Answer · answered Feb 01 '18 at 14:29

Absolutely, there are multiple ways to concatenate this. Here is one method:

SELECT
    T1.U_BaseCode,
    STUFF((
        SELECT
            ',' + CONVERT(VARCHAR(10), T2.U_FilterCode) AS [text()]
        FROM [TableName] T2
        WHERE T2.U_BaseCode = T1.U_BaseCode
        ORDER BY T2.U_FilterCode
        FOR XML PATH('')
    ), 1, 1, '') AS FilterCodes
FROM [TableName] T1
GROUP BY
    T1.U_BaseCode

SQL Cursor merge fields

1 Answers1