Display column of different rows as column of a single row in SQL Server

Problem
Displaying column value of different rows in a single row with a separator comma, hyphen, semicolon etc.

Solution
In the below solution, refer the table "PRODUCT". In the table there are multiple products for a client and we are trying to display products as comma separated value for each client as a single row.

Table and Data Script

?

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE PRODUCT (       ClientNumber    VARCHAR(50)     , ClientName    VARCHAR(50)     , Product        VARCHAR(50) )   INSERT INTO PRODUCT SELECT '100SON', 'Sony', 'TV' UNION ALL SELECT '100SON','Sony', 'DVD Player' UNION ALL SELECT '100SON','Sony', 'Cell Phone' UNION ALL SELECT '200KEN','Kenmoore', 'Microwave' UNION ALL SELECT '200KEN','Kenmoore', 'Dryer'

Table Data

Now, lets write a query to display the data of product as a single row on the basis of Client Number and Client Name.

?

1 2 3 4 5 6 7 8

SELECT ClientNumber, ClientName        , STUFF(( SELECT ',' + Product                  FROM PRODUCT b                  WHERE a.ClientNumber = b.ClientNumber                  FOR XML PATH('')          ), 1, 1, '') AS ProductList FROM  PRODUCT a GROUP BY ClientNumber, ClientName

OUTPUT

redapple24