Combine values of a column in a group by

Question

tblA has columns EOD_Job_ID and Firm_ID

tblA
EOD_Job_ID Firm_ID
1          111
1          222
2          333
2          444

How to group by EOD_Job_ID and combine the Firm_IDs?

Desired result:

EOD_Job_ID Firm_ID
1          111,222
2          333,444

Try this link [How to concatenate text from multiple rows into a single text string in SQL server](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — zip, Nov 27 '19 at 19:44

score 0 · Answer 1 · answered Nov 27 '19 at 19:44

I'd suggest to use the FOR XML PATH method to concatenate strings. Just make sure in case your Firm_ID is of integer type you cast it to (n)nvarchar first

SELECT DISTINCT EOD_Job_ID, STUFF((SELECT ',' + CAST(Firm_ID AS nvarchar(3)) 
                                   FROM @tblA
                                   WHERE EOD_Job_ID = tbl.EOD_Job_ID FOR XML PATH('')),1,1,'') AS Firm_ID
FROM @tblA tbl

score 0 · Answer 2 · answered Nov 27 '19 at 19:46

If you adjust the code from that link to you table, it will be:

 SELECT Main.EOD_Job_ID,
       LEFT(Main.Students,Len(Main.Students)-1) As Students
FROM
    (
        SELECT DISTINCT ST2.EOD_Job_ID, 
            (
                SELECT cast(ST1.Firm_ID as varchar(5)) + ',' AS [text()]
                FROM tblA ST1
                WHERE ST1.EOD_Job_ID = ST2.EOD_Job_ID
                ORDER BY ST1.EOD_Job_ID
                FOR XML PATH ('')
            ) [Students]
        FROM tblA ST2
    ) [Main]

Combine values of a column in a group by

2 Answers2