quote:

---

Originally posted by vgr.raju

use northwind
go
declare @table table(customerid varchar(1000),orderlist varchar(1000),productdetails varchar(1000))
declare @c varchar(1000),@cnext varchar(1000),@o varchar(1000),@onext varchar(1000)
declare @p varchar(1000),@pnext varchar(1000)
declare cursorcustomer cursor for
select customers.customerid,orders.orderid,[order details].productid from customers
join orders on customers.customerid=orders.customerid
join [order details] on [order details] .orderid=orders.orderid
order by customers.customerid,orders.customerid,[order details].orderid
open cursorcustomer
fetch next from cursorcustomer into @cnext,@onext,@pnext
set @c=@cnext
while @@fetch_status =0
begin
if @c<@cnext
begin
insert @table select @c,@o,@p
select @c=@cnext,@o=@onext
end
else
set @o=coalesce(@o+' : ',space(0))+@onext
set @p=@pnext
if @p<@pnext
begin
insert @table select @c,@o,@p
select @p=@pnext
end
else
set @p=coalesce(@p+' : ',space(0))+@pnext

fetch next from cursorcustomer into @cnext,@onext,@pnext
end
insert @table select @c,@o,@p
close cursorcustomer
deallocate cursorcustomer
select * from @table



I have written the above query.The results are given below:
ALFKI 10643 : 10643 : 10643 : 10692 : 10702 : 10702 : 10835 : 10835 : 10952 : 10952 : 11011 : 11011 71 : 71
ANATR 10308 : 10308 : 10625 : 10625 : 10625 : 10759 : 10926 : 10926 : 10926 : 10926 72 : 72



It gives the customername,orderid,and productid.
I would like to get distinct orderid and and their respective product id .

for example:
customerid orderid list productidlist
I want only three columns .
If somebody can help me how to get the desired results using cursor,i would be really greatful.

---