sql-serverstringreportingaggregatesfor-xml

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


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

Solution

  • 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