CODEWITHSUNDEEP
sqlsql-servert-sql
user5947977
user5947977

Reputation: 21

SQL Concatenate and group

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.

Upvotes: 1

Views: 141

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

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

Upvotes: 1

LONG
LONG

Reputation: 4610

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

Upvotes: 0

indiri
indiri

Reputation: 332

In MSSQL Synax:

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

Upvotes: 1

Related Questions