How to pivot text fields into one in SQL Server?

Question

Given the following simple example

create table #example (
ProductID int,
ProductName varchar(100),
CustomerID int
)

insert #example values 
    (1,'Product 1',100),
    (2,'Product 2',100),
    (3,'Product 3', 101),
    (3,'Product 3', 102)

I want to see a result set that has CustomerID, ProductNamesBought where the products are comma separated. e.g.

CustomerID ProductNamesBought
========== ====================
100        Product 1, Product 2
101        Product 3
102        Product 3

Answer 1

SELECT  e.CustomerID, 
        STUFF((SELECT ','+ProductName FROM #example WHERE e.CustomerID = CustomerID FOR XML PATH('')),1,1,'') as ProductNamesBought
FROM #example e
GROUP BY e.CustomerID

Output:

CustomerID  ProductNamesBought
100         Product 1,Product 2
101         Product 3
102         Product 3