Columns in CSV Format

Eg: The Popular Northwind Database.

Combine the Products and Orderdetails table with the Productid to get the Productname and name a new table as Product_orders

The Join is as follows


select
od.*,p.productname
from
[order details] od
join
products p
on
od.productid=p.productid
order by od.orderid

The Data is as follows




Now If you need all the products and the Quantity in per orderid in a single column like



so the Table looks like

OrderID Productname
343434 Product1,Quantity||Product2,Quantity||Product3,Quantity

Here is the Code Sample to do the Reporting Structure


--Create the table for orderid and allproducts as

create table result
(
orderid int
,Values_CSV varchar(8000)
)


--Use the Following Cursor Code


declare csno cursor for
select distinct orderid from product_orders

declare @snos int

open csno
fetch next from csno into @snos

declare @allvalues varchar(8000)

while @@fetch_status = 0

begin

set @allvalues=null
select @allvalues =isnull(@allvalues+'||','') + [productname]+','+convert(varchar(50),[quantity])
from product_orders
where orderid = @snos

insert into result values(@snos,@allvalues)

fetch next from csno into @snos

end

close csno
deallocate csno