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? — Tab Alleman
– Tab Alleman, Commented Oct 18, 2017 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
– Eric, Commented Oct 18, 2017 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
– Md. Suman Kabir, Commented Oct 18, 2017 at 17:44
@user5947977 - Please mark the answer as useful(upvote) also as it worked for your issue. — Md. Suman Kabir
– Md. Suman Kabir, Commented Oct 19, 2017 at 7:28
thank you all, noted for future, the answer below worked a treat :) — user5947977
– user5947977, Commented Oct 19, 2017 at 7:29

Md. Suman Kabir · Accepted Answer · 2017-10-18 17:23:50Z

1

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

answered Oct 18, 2017 at 17:23

Md. Suman Kabir

5,4515 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

indiri · Accepted Answer · 2017-10-18 17:11:09Z

1

In MSSQL Synax:

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

answered Oct 18, 2017 at 17:11

indiri

3321 silver badge9 bronze badges

1 Comment

jradich1234 Over a year ago

This method assumes you using SQL Server 2017

LONG · Accepted Answer · 2017-10-18 17:17:00Z

0

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

answered Oct 18, 2017 at 17:17

LONG

4,6302 gold badges21 silver badges41 bronze badges

Collectives™ on Stack Overflow

SQL Concatenate and group

The output should look like this:

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

The output should look like this:

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related