SQL Concatenate and group

Question

I need a TSQL version of group_concat Simmilar to the example found here:

Policy   Destination   ID
-------------------------
PolA     DestA         1    
PolA     DestA         2    
PolB     DestB         3     
PolB     DestB         4    
PolC     DestC         5
PolC     DestC         6
PolC     DestD         7

The output should look like this:

PolA   DestA   1,2
PolB   DestB   3,4
PolC   DestC   5,6
PolC   DestD   7

The grouping is on the uniqueness of both the first 2 columns, and then a concatenated output on the third.

I found this link but it only take into account 2 columns

Any help would be appreciated.

Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Tab Alleman, Oct 18 '17 at 17:26
Please tag which dbms you are using. If this is SQL Server, add the tag. Also, tag which SQL Server you are using. STRING_AGG may not exist in earlier version. Secondly, what have you tried so far? — Eric, Oct 18 '17 at 17:37
As he wrote `TSQL` in the question, we can assume the DBMS as `SQL Server` and i suggested that edit already. @user5947977 - Did you try my answer? — Md. Suman Kabir, Oct 18 '17 at 17:44
@user5947977 - Please mark the answer as useful(upvote) also as it worked for your issue. — Md. Suman Kabir, Oct 19 '17 at 07:28
thank you all, noted for future, the answer below worked a treat :) — user5947977, Oct 19 '17 at 07:29

score 1 · Answer 1 · answered Oct 18 '17 at 17:11

1

In MSSQL Synax:

SELECT Policy, Destination, STRING_AGG ( [ID], ',' ) IDs
FROM Table

answered Oct 18 '17 at 17:11

indiri

332
1
9

This method assumes you using SQL Server 2017 – jradich1234 Oct 18 '17 at 17:23

score 1 · Accepted Answer · answered Oct 18 '17 at 17:23

You can try this :

SELECT G.Policy, G.Destination,
    stuff(
    (select cast(',' as varchar(max)) + U.ID
    from yourtable U
    WHERE U.Policy = G.Policy and U.Destination = G.Destination
    order by U.Policy
    for xml path('')
    ), 1, 1, '') AS IDs
FROM yourtable G group BY G.Policy, G.Destination

score 0 · Answer 3 · answered Oct 18 '17 at 17:17

I just create the PolA example table for you, just replace the CTE as your table, try below:

WITH ABC
as
(
select   'PolA' as Policy,'DestA' as Destination,'1' as ID  
UNION ALL 
select 'PolA','DestA','2'
)

SELECT Policy, Destination,
 STUFF((SELECT ',' + A.ID FROM ABC as A WHERE A.Policy = B.Policy FOR XML PATH('')),1,1,'') as ID
FROM ABC as B
GROUP BY B.policy, B.Destination

SQL Concatenate and group

The output should look like this:

3 Answers3