转换行的一列只有一分列在SQL Server查询

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 

Answer 2

使用下面的查询所需的输出。

SELECT tblplantitle.id 
     , STUFF((SELECT ', ' + CAST(tblplantoproductpayment.productid AS VARCHAR(10)) [text()] 
       FROM tblplantoproductpayment 
       WHERE tblplantoproductpayment.planid = tblplan.id 
       FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') List_ProductIds 
FROM tblplantitle 
     INNER JOIN tblplan on tblplan.plantitleid = tblplantitle.id 
GROUP BY tblplantitle.id