How to pivot and merge to avoid the NULL cells?-CodePudding

Sample table data:

SELECT [id],[date],[agent],[sales]
  FROM [AgentSales]

id  date                    agent   sales
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

Group by YearMonth, Agent:

SELECT CAST(YEAR([DATE]) AS VARCHAR) ' ' CAST(MONTH([DATE]) AS VARCHAR) YRMON, AGENT, SUM(SALES) SALES
  FROM AgentSales
  GROUP BY  CAST(YEAR([DATE]) AS VARCHAR) ' ' CAST(MONTH([DATE]) AS VARCHAR), AGENT
  ORDER BY 1

YRMON   AGENT   SALES
2021 1  Agent A 12
2021 1  Agent B 27
2021 2  Agent A 1
2021 2  Agent B 33
2021 3  Agent A 11
2021 3  Agent B 3

PIVOT:

  SELECT CAST(YEAR([DATE]) AS VARCHAR) ' ' CAST(MONTH([DATE]) AS VARCHAR) YRMON, [AGENT A], [AGENT B]
  FROM AgentSales
  PIVOT (SUM(SALES) FOR AGENT IN ([AGENT A], [AGENT B])) AS PIVOTTABLE

YRMON   AGENT A AGENT B
2021 1  10      NULL
2021 1  2       NULL
2021 1  NULL    22
2021 1  NULL    5
2021 2  1       NULL
2021 2  NULL    33
2021 3  11      NULL
2021 3  NULL    3

I want to have the actual value in place of the NULLs. So effectively there should be only 2 rows for each YearMon. How to do this?

Expected result:

  YRMON   AGENT A AGENT B
  2021 1  12      27
  2021 2  1       33
  2021 3  11      3

CodePudding user response：

Looks like simple conditional aggregation should do the trick.

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]);

Or if you want each one on a separate row, you can add in a row-number and group on that

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;

Grouping by EOMONTH is more performant than grouping by a string

db<>fiddle