1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
agnanzakariya
  • 49
  • 10
  • Please tag your question with the database you are running: mysql, oracle, sqlserver...? – GMB Oct 06 '20 at 13:43

1 Answers1

3

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Hi, thanks for your answer, that's real quick, but my question not clear yet, I've been edited my question because I have one field with same content but want to show just one content only (look field "color" for example) – agnanzakariya Oct 06 '20 at 14:02
  • @agnanzakariya: just add `color` to the `select` clause and to the `group by` clause. – GMB Oct 06 '20 at 14:03