Monday, April 2, 2012

How to create comma delimited list from table column || Display multiple row data from database into single row

I have two tables which are advertiser and orders.The advertiser table has multiple order's data.so here my requirement is have  to display the order id's list of advertiser with comma delimited in sql.For this i have used XML path elements in sql.
SELECT DISTINCT Advertiser_Id,
(substring( (SELECT ', ' + cast(Order_Id as nvarchar)FROM Dfp_Main_Data e2 WHERE e2.Advertiser_Id = e1.Advertiser_Id group by Order_Id FOR XML path(''), elements),2,500)) as orders ,
o.Notes FROM Dfp_Main_Data e1,Dfp_Orders o  where e1.Order_Id=o.OrderId and e1.Advertiser_Id='"+adverid+"'";

No comments:

Bel