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

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

redapple24