By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE AgentSales (
"id" INTEGER,
"date" VARCHAR(34),
"agent" varchar(10),
"sales" int
);
INSERT INTO AgentSales
("id", "date", "agent", "sales")
VALUES
('1', '2021-01-02 00:00:00.000', 'Agent A', 10),
('2', '2021-01-03 00:00:00.000', 'Agent A', 2),
('3', '2021-01-04 00:00:00.000', 'Agent B', 22),
('4', '2021-01-06 00:00:00.000', 'Agent B', 5),
('5', '2021-02-05 00:00:00.000', 'Agent A', 1),
('6', '2021-02-06 00:00:00.000', 'Agent B', 33),
('7', '2021-03-06 00:00:00.000', 'Agent A', 11),
('8', '2021-03-06 00:00:00.000', 'Agent B', 3);
8 rows affected
SELECT
FORMAT(EOMONTH([DATE]), 'yyyy MM') YRMON,
SUM(CASE WHEN AGENT = 'AGENT B' THEN SALES END) [AGENT B],
SUM(CASE WHEN AGENT = 'AGENT A' THEN SALES END) [AGENT A]
FROM AgentSales
GROUP BY EOMONTH([DATE])
ORDER BY EOMONTH([DATE]);
| YRMON | AGENT B | AGENT A |
|---|---|---|
| 2021 01 | 27 | 12 |
| 2021 02 | 33 | 1 |
| 2021 03 | 3 | 11 |
Warning: Null value is eliminated by an aggregate or other SET operation.
SELECT
FORMAT(EOMONTH([DATE]), 'yyyy MM') YRMON,
SUM(CASE WHEN AGENT = 'AGENT B' THEN SALES END) [AGENT B],
SUM(CASE WHEN AGENT = 'AGENT A' THEN SALES END) [AGENT A]
FROM (
SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY EOMONTH([DATE]), AGENT ORDER BY [DATE])
FROM AgentSales
) sales
GROUP BY EOMONTH([DATE]), rn
ORDER BY EOMONTH([DATE]), rn;
| YRMON | AGENT B | AGENT A |
|---|---|---|
| 2021 01 | 22 | 10 |
| 2021 01 | 5 | 2 |
| 2021 02 | 33 | 1 |
| 2021 03 | 3 | 11 |
Warning: Null value is eliminated by an aggregate or other SET operation.