How to roll up multiple rows into a single row and column

Question

I referenced This and others stacks

This is what I have done:

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

Error:

Column 'BackEndEfficiency.ReportID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why am I getting this error as I want the ReportDate to be grouped by?

Example of data structure

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

I would like to get this output:

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

please do not post image, post as text please – Squirrel Mar 17 '20 at 00:49 — Squirrel, Mar 17 '20 at 00:49

score 0 · Accepted Answer · answered Mar 17 '20 at 00:51

0

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;

answered Mar 17 '20 at 00:51

Gordon Linoff

1,242,037
58
646
786

Should be using Date = Date instead of ID = ID. My mistake. – Chung Yau Mar 17 '20 at 01:11

How to roll up multiple rows into a single row and column

1 Answers1