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
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 |