If you are using SQL 2005 and above you could do something like this:
SELECT DISTINCT customerId, (SELECT CONVERT(VARCHAR(MAX), (SELECT LTRIM(UPPER(ISNULL(ProductName,'')) + ',')
FROM tblOrder o
WHERE o.CustomerId = orders.CustomerId
FOR XML PATH('')))) AS Products
FROM tblOrder orders
ORDER BY CustomerId
OR using STUFF()
SELECT DISTINCT customerId, STUFF((SELECT ',' + ProductName FROM tblOrder FOR XML PATH('')), 1, 1, '') AS Products
FROM tblOrder orders
ORDER BY CustomerId