How to group the field in 1 line in MS SQL

Question

I'm using this MS SQL Script:

     select NumberId, ART_No, InkColor 
     from INK_STOCK

and I have an output like this:

     NumberId         ART_No          InkColor
     ------------------------------------------
     0010             23003             BLUE
     0010             23003             RED
     0010             23003             GREEN
     0013             23004             ORANGE
     0013             23004             PINK
     0013             23004             WHITE
     0015             23007             GREEN
     0015             23007             PINK

How can I make the output should be like this:

     NumberId         ART_No          InkColor
     ------------------------------------------
     0010             23003             BLUE, RED, GREEN
     0013             23004             ORANGE, PINK, WHITE
     0015             23007             GREEN, PINK

Thanks

http://explainextended.com/2010/06/21/group_concat-in-sql-server/ — fancyPants, Jan 23 '13 at 10:35

score 5 · Accepted Answer · answered Jan 23 '13 at 10:39

Query:

SQLFIDDLEexample

select c1.NumberId, c1.ART_No,
    STUFF((   SELECT ', ' +x.InkColor
        FROM INK_STOCK x
        WHERE c1.NumberId = x.NumberId
        FOR XML PATH ('')
    ),1,1,'') as InkColor
from INK_STOCK c1
group by c1.NumberId, c1.ART_No

Result:

| NUMBERID | ART_NO |             INKCOLOR |
--------------------------------------------
|       10 |  23003 |     BLUE, RED, GREEN |
|       13 |  23004 |  ORANGE, PINK, WHITE |
|       15 |  23007 |          GREEN, PINK |

How to group the field in 1 line in MS SQL

1 Answers1