Change the format of the output using the adventureworks2008R2 database

Question

I need to change the format of the output in following format:

43659 709, 711, 712, 714, 716, 771, 772, 773, 774, 776, 777, 778

43660 758, 762

43661 708, 711, 712, 715, 716, 741, 742, 743, 745, 747, 773, 775, 776, 777, 778

How do I use Pivot/Unpivot for the following query? Or is there any other way to do it?

Select Distinct ProductID, SalesOrderID 
from Sales.SalesOrderDetail 
Group By SalesOrderID, ProductID 
Order By SalesOrderID, ProductID

Answer 1

You do need to cast productid

SELECT      distinct
            t1.SalesOrderID AS [salesorderid],
            STUFF((    SELECT concat(',' , cast(t2.productid as varchar(10))) AS [text()]
                        FROM Sales.SalesOrderDetail t2
                        WHERE
                        t1.salesorderid = t2.salesorderid
                        order by t2.productid
                        FOR XML PATH('') 
                        ), 1, 1, '' )

            AS [products]
FROM  Sales.SalesOrderDetail t1
where salesorderid in(43659,43660,43661)

salesorderid products
------------ ----------------------------------------------------------
43659        709,711,712,714,716,771,772,773,774,776,777,778
43660        758,762
43661        708,711,712,715,716,741,742,743,745,747,773,775,776,777,778

(3 row(s) affected)