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

Question

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 1

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

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

Sign up