Merge Multiple Row Description With Same ID in One Row on SQL

Question

Trust me this is classic but I don't know how to resolve this:

This is my data source

    ID   Color  Description 
    -----------------------  
    10   White  MR    
    10   White  DREAM  
    10   White  TURTLENIP  

My output data expected show like this:

    ID   Color   Description 
    ------------------------  
    10   White   MR, DREAM, TURTLENIP    

I use COALESCE but it seem not work for this report.

P.S this is simple question from my case, my query lot of inner join to merge another table in one report. But I don't know how to merge several row Desc to merge into one row.

Answer 1

You want string aggregation. If you are running SQL Server 2017 or higher, you can use string_agg():

select id, string_agg(descr, ', ') all_descr
from mytable
group by id

Note that desc is a language keyword (as in order by ... desc), hence not a good choice for a column name. I renamed it to descr in the query.