Display column of different rows as column of a single row

Display column of different rows as column of a single row:


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.

               

• Mechanism
• Microsoft Updates
• Microsoft Word
• A Single

Table and Data Script :

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.

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 :