sql - 如何将多行汇总成单行和单列

Question

我引用了这个和其他堆栈

这就是我所做的：

SELECT 
    ReportDate,  
    ReportID = STUFF((SELECT ',' + CAST(t1.ReportID AS varchar(50)) 
                      FROM BackEndEfficiency t1
                      WHERE t1.ReportID = t2.ReportID
                      FOR XML PATH ('')), 1, 1, '') 
FROM
    BackEndEfficiency t2
GROUP BY
    ReportDate

错误：

列 'BackEndEfficiency.ReportID' 在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

为什么我会收到此错误，因为我希望将ReportDate其分组？

数据结构示例

ReportDate  ReportID
--------------------
2020-03-11  30
2020-03-11  31
2020-03-16  32
2020-03-16  33

我想得到这个输出：

ReportDate  ReportID
--------------------
2020-03-11  30,31
2020-03-16  32,33

标签： sqlsql-server

Answer 1

I think you want ReportDate for the correlation clause. I would recommend:

select bee.ReportDate,
       stuff((select ',' + CAST(t1.ReportID as varchar(50)) 
              from BackEndEfficiency bee2
              where bee2.ReportDate = bee.ReportDate
              for xml path ('')
             ), 1, 1, '') 
from (select bee.ReportDate
      from BackEndEfficiency bee
     ) bee;