Grouping by and getting multiple rows to one cell separated by commas

Question

I know that title is ambigious and I know that there are similiar questions here. I looked and could not made anything work exactly as I need.

I need to Group By ReportNumber and those sharing one category add to one cell.

My DB:

  ID  |      CategorySymbol    |     ReportNumber   |   NumberInCategory    

   1             A                       31                101
   2             B                       31                107
   3             C                       31                121
   4             A                       32                191
   5             A                       33                165
   6             B                       32                156
   7             C                       32                127
   8             A                       31                166
   9             B                       31                177

Desired result:

  ReporNumber  |         CategoryA         |       CategoryB        |     CategoryC       

   31                      **101,166**                   **107,177**                121
   32                      191                       156                    127
   33                      165                       NULL                   NULL

One of many different attempts:

select ReportNumber,
  CategoryFirst, CategorySecond, CategoryThird, CategoryFourth
from
(
  select NumberInCategory, Report.value('(/Report/@ReportNumber)[1]', 'nvarchar(max)') AS ReportNumber,
    'Category' + cast(CategorySymbol as varchar(10)) CategorySymbol
  from dbo.ReportDB t
) d
pivot
(
    max(NumberInCategory)
  for CategorySymbol in (CategoryFirst, CategorySecond, CategoryThird, CategoryFourth)
) piv;

My result:

  ReporNumber  |         CategoryA         |       CategoryB        |     CategoryC       

   31                      **166**                       **177**                    121
   32                      191                       156                    127
   33                      165                       NULL                   NULL

It is obvious why the result is how it is - max(NumberInCategory). The only question is how do I get to make a query that selects Number based on the CategorySymbol in for loop. I tried doing funtions that return one result like STUFF or simple SELECT but couldn't do them properly. It does not let me replace max(NumberInCategory).

E.g. something like that:

STUFF((SELECT ', ' + CAST(NumberInCategory AS VARCHAR(10)) [text()], Report.value('(/Report/@ReportNumber)[1]', 'nvarchar(max)') AS ReportNumber
         FROM ReportDB
         WHERE ReportNumber = t.ReportNumber
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output

Answer 1

Using the stuff() with select ... for xml path ('') method of string concatenation before pivot():

;with t as (
  select 
      NumberInCategory
    , Report.value('(/Report/@ReportNumber)[1]', 'nvarchar(max)') AS ReportNumber
    , CategorySymbol
  from dbo.ReportDB
)
select ReportNumber, CategoryA, CategoryB, CategoryC
from (
  select
      t.ReportNumber
    , CategorySymbol = 'Category'+convert(varchar(10),t.CategorySymbol)
    , NumberInCategory = stuff((
        select ', '+convert(varchar(13),i.NumberInCategory)
        from t i
        where i.ReportNumber = t.ReportNumber
          and i.CategorySymbol = t.CategorySymbol
        order by i.NumberInCategory
        for xml path (''), type).value('(./text())[1]','nvarchar(max)')
      ,1,2,'')
  from t
  group by t.ReportNumber, t.CategorySymbol
  ) s
  pivot (max(NumberInCategory)
    for CategorySymbol in (CategoryA, CategoryB, CategoryC)
  ) piv;

rextester demo: http://rextester.com/OSAZ69656

returns:

+--------------+-----------+-----------+-----------+
| ReportNumber | CategoryA | CategoryB | CategoryC |
+--------------+-----------+-----------+-----------+
|           31 | 101, 166  | 107, 177  | 121       |
|           32 | 191       | 156       | 127       |
|           33 | 165       | NULL      | NULL      |
+--------------+-----------+-----------+-----------+

Answer 2

Here's a slightly different flavor...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    ID INT NOT NULL PRIMARY KEY CLUSTERED,
    CategorySymbol CHAR(1) NOT NULL,
    ReportNumber int NOT NULL,
    NumberInCategory INT NOT NULL 
    );
INSERT #TestData(ID, CategorySymbol, ReportNumber, NumberInCategory) VALUES
    (1, 'A', 31, 101),
    (2, 'B', 31, 107),
    (3, 'C', 31, 121),
    (4, 'A', 32, 191),
    (5, 'A', 33, 165),
    (6, 'B', 32, 156),
    (7, 'C', 32, 127),
    (8, 'A', 31, 166),
    (9, 'B', 31, 177);

-- SELECT * FROM #TestData td;

--==================================================================================

SELECT 
    td1.ReportNumber,
    CategoryA = MAX(CASE WHEN td1.CategorySymbol = 'A' THEN STUFF(c.CSV, 1, 1, '') END),
    CategoryB = MAX(CASE WHEN td1.CategorySymbol = 'B' THEN STUFF(c.CSV, 1, 1, '') END),
    CategoryC = MAX(CASE WHEN td1.CategorySymbol = 'C' THEN STUFF(c.CSV, 1, 1, '') END)
FROM
    #TestData td1
    CROSS APPLY (
                (SELECT 
                    CONCAT(',', td2.NumberInCategory)
                 FROM
                    #TestData td2
                WHERE 
                    td1.CategorySymbol = td2.CategorySymbol
                    AND td1.ReportNumber = td2.ReportNumber
                FOR XML PATH(''))
                ) c (CSV)
GROUP BY
    td1.ReportNumber;

Results...

ReportNumber CategoryA  CategoryB  CategoryC
------------ ---------- ---------- ----------
31           101,166    107,177    121
32           191        156        127
33           165        NULL       NULL