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

Unless you have ported adventureworks to mysql this is a sqlserver question and you should change the tags. — P.Salmon, Jul 08 '18 at 08:26
Possible duplicate of [How Stuff and 'For Xml Path' work in Sql Server](https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server) — P.Salmon, Jul 08 '18 at 08:29
@P.Salmon I tried this for my query. But I'm getting an error "Conversion failed when converting the varchar value ',' to data type int." Is this because the SalesOrderID column's datatype is int? — porsh01, Jul 08 '18 at 18:17

score 2 · Accepted Answer · answered Jul 09 '18 at 12:07

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)

Change the format of the output using the adventureworks2008R2 database

1 Answers1