sql - 在 SQL Server 查询中将一列 Rows 转换为只有一列列

Question

我在 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

结果是这样的:

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

但我想要这个结果:

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

我怎样才能得到那个结果？

Answer 1

试试这个:

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