OK. I created data for the last of your sample data.

INsert into @OrderDetail Values ( 10, 500)

INsert into @OrderDetail Values ( 20, 500)

INsert into @OrderDetail Values ( 30, 500)

Now to retrieve what you are looking for. First I got the list of products with duplicates. Then I get the orders associated with those products. Last but not least generate the delimited list from the values.

;with Products

as

(

select ProductID, COUNT(*) as MyCount

from @Orderdetail

group by ProductID

having COUNT(*) > 1

)

, ProductOrders as

(

select o.OrderID, o.ProductID

from @Orderdetail o

join Products p on o.ProductID = p.ProductID

)

select p1.ProductID,

STUFF((select ',' + cast(OrderID as varchar(5))

from ProductOrders p2

where p2.ProductID = p1.ProductID

order by p2.ProductID

for xml path('')), 1, 1, ' ') as OrderList

from ProductOrders p1

group by p1.ProductID