Click here to Skip to main content
14,691,272 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Sir

I have table in the Following Format.

tblOrder
----------
CustomerId ProductName
========== ==========
1 Soap
2 Cold-Drinks
1 Sugar
1 Milk
2 Biscuits

Now i want to write a query which will display the result like

CustomerID ProductName
1 Soap,Sugar,Milk
2 Cold-Drinks,Biscuits

Plz help me Sir.

Thank You
Posted

Try this
SELECT GROUP_CONCAT(ProductName) FROM tblOrder GROUP BY CustomerId
   
Comments
db7uk 29-May-12 9:55am
   
The Group_CONCAT is only MySQL and not sql server.
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
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900