CODEWITHSUNDEEP
sqlsql-servert-sql
Dominic
Dominic

Reputation: 175

Create comma separated group list from rows

I need to extract a comma separated list from the following:

enter image description here

Return should gives something like

1 : Route#1, Route#2

2 : Route#3, Route#4

3 : Route#5

4 : Route#6

I'm struggling with the STUFF function. So far I have:SELECT STUFF( (SELECT DISTINCT ';' + T4.[Outbound Trucks] FROM #TEMP4 T4 FOR XML PATH('') ),1,1,'') AS MasterRoutes

Result gives me enter image description here

any help would be really appreciated.

thanks a lof for your time

Upvotes: 0

Views: 73

Answers (1)

Esperento57
Esperento57

Reputation: 17462

If your sql server is 2017 or more :

SELECT dorno, STRING_AGG ([Outbound Trucks], ',') as CSV
FROM #TEMP4
GROUP BY dorno

Explanation Here

In you case you can eliminate doublon to like this:

with tmp as (
select distinct dorno, [Outbound Trucks] Truck from #TEMP4
)
SELECT dorno, STRING_AGG (Truck, ',') as CSV
FROM tmp
GROUP BY dorno

Upvotes: 1

Related Questions