示例表資料：
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
按年分組，代理：
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
樞：
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
我想用實際值代替 NULL。因此，每個 YearMon 應該只有 2 行。這該怎么做？
預期結果：
YRMON AGENT A AGENT B
2021 1 12 27
2021 2 1 33
2021 3 11 3
uj5u.com熱心網友回復：
看起來簡單的條件聚合應該可以解決問題。
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]);
或者，如果您希望每個都在單獨的行上，您可以添加一個行號并在其上分組
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;
按分組
EOMONTH比按字串分組更高效
資料庫<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/311525.html
標籤：查询语句 枢 sql-server-2019
上一篇：使用SUMOVER子句，如何僅在輸出不大于某個值時檢查期間的總和，否則使用當前月份值？
下一篇：對新創建的列使用CASEWHEN