How do I group by in a string for reporting measures by category in said string

Question

Using SQL Server 2019, I am trying to aggregate rep ratings by quarter onto a single row. So the consumer can visualize in a single row ratings by quarter.

So the output is like:

/*  rep      |   ratings                           |      quartc               */
/*  ------   |   -------------------------------   |      ---------------      */
/*  911911   |   1,2,2,2,1 | 2,2,2,2,1 | 2,2,1,2   |      Q1M | Q2M | Q3M      */

or better like

/*  rep      |   Qtr: ratings                                  */
/*  ------   |   ----------------------------------------
/*  911911   |   Q1M: 1,2,2,2,1 Q2M:2,2,2,2,1 Q3M:2,2,1,2      */

I've made progress using for xml and stuff, but I can't seem get my head around how to group by quarter inside the ratings string. Here is some runnable code for experimentation if you've got a good idea.

/*  THE DATA */
CREATE TABLE [dbo].[RepRatings]
(
    [row] [bigint] NULL,
    [rep] [int] NOT NULL,
    [quartc] [varchar](3) NOT NULL,
    [rate] [varchar](3) NULL
) ON [PRIMARY]

INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (1, 911911, N'Q1M', N'1');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (2, 911911, N'Q1M', N'1');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (3, 911911, N'Q1M', N'2');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (4, 911911, N'Q1M', N'2');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (5, 911911, N'Q1M', N'2');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (6, 911911, N'Q1M', N'1');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (1, 911911, N'Q2M', N'2');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (2, 911911, N'Q2M', N'2');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (3, 911911, N'Q2M', N'2');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (4, 911911, N'Q2M', N'2');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (5, 911911, N'Q2M', N'1');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (6, 911911, N'Q2M', N'1');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (1, 911911, N'Q3M', N'2');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (2, 911911, N'Q3M', N'2');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (3, 911911, N'Q3M', N'2');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (4, 911911, N'Q3M', N'1');
INSERT [dbo].[RepRatings] ([row], [rep], [quartc], [rate]) VALUES (5, 911911, N'Q3M', N'2');
GO

/*TRIED SO FAR, IS CLOSE */
SELECT
    [RefRowInQtr] = [row], A.rep,
    ratings = STUFF((SELECT ',' + B.rate
                     FROM RepRatings B
                     WHERE A.rep = B.rep
                     FOR XML PATH('')), 1, 1, ''), 
    quartc = STUFF((SELECT DISTINCT '  ' + B.quartc
                    FROM RepRatings B
                    WHERE A.rep = B.rep
                    FOR XML PATH('')), 1, 1, '')
FROM
    RepRatings A

Answer 1

Assuming this is SQL Server version 2017 or higher, you can use STRING_AGG instead of FOR XML PATH :

SELECT
    rep, 
    STRING_AGG(rates, '|') AS ratings, 
    STRING_AGG(quartc, '|') AS quartc
FROM
    (SELECT
         rep, quartc, STRING_AGG(rate, ',') AS rates
     FROM
         [RepRatings]
     GROUP BY
         rep, quartc) s
GROUP BY
    rep

Results :

rep	ratings	quartc
911911	1,1,2,2,2,1|2,2,2,2,1,1|2,2,2,1,2	Q1M|Q2M|Q3M

OR :

select rep, STRING_AGG(CONCAT(quartc, ': ', rates), '|') as ratings
from (
  select rep, quartc, STRING_AGG(rate, ',') as rates
  from [RepRatings]
  group by rep, quartc
) s
group by rep

Result :

rep	ratings
911911	Q1M: 1,1,2,2,2,1|Q2M: 2,2,2,2,1,1|Q3M: 2,2,2,1,2

Demo here