atabrizi atabrizi - 3 months ago 7
SQL Question

Convert a column of Rows to only one column column in SQL Server query

I have this query in SQL Server 2012 :

select
tblplantitle.id, tblplantoproductpayment.productid
from
tblplantitle
inner join
tblplan on tblplan.plantitleid = tblplantitle.id
inner join
tblplantoproductpayment on tblplantoproductpayment.planid = tblplan.id
group by
tblplantitle.id, tblplantoproductpayment.productid


The result is like this :

id productid
1 1
1 2
1 3
1 10
2 5
2 1
3 4
3 11


but I want this result :

1 1,2,3,10
2 5,1
3 4,11


How can I get that result ?

Answer

Try this:

WITH cte as 
(
select 
    tblplantitle.id, tblplantoproductpayment.productid
from 
    tblplantitle 
inner join  
    tblplan on tblplan.plantitleid = tblplantitle.id
inner join 
    tblplantoproductpayment on tblplantoproductpayment.planid = tblplan.id
group by 
    tblplantitle.id, tblplantoproductpayment.productid
)
SELECT id, productid = 
    STUFF((SELECT ', ' + productid
           FROM cte b 
           WHERE b.id= a.id
          FOR XML PATH('')), 1, 2, '')
FROM cte a
GROUP BY id