在SQL Server查询中将一列行转换为只有一列的列

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

结果如下：

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

我该如何获得那个结果？

- atabrizi

---

你听说过数据透视表吗？ - Kalu Singh Rao

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

- Chaos Legion

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

- Unnikrishnan R