带有月份和年份的 SQL 数据透视表

Question

我正在尝试使用 SQL Pivot 对一些装运数据进行排序，但我无法弄清楚。我以这种方式对数据进行了排序(一行是从 2015 年到 )每年每个月为一个家庭运送的元素总数:

表A

Year | Month | ItemFamilyCode | TotalShipped
2018 |   9   |    FA01        |       5
2018 |   9   |    FA04        |       4
2018 |   10  |    FA01        |       2
2018 |   11  |    FA02        |       1
2018 |   12  |    FA03        |       3
2019 |   1   |    FA04        |       7

等等。我想达到以下结果:

ItemFamilyCode | 2018-9 | 2018-10 | 2018-11 | 2018-12 | 2019-1 | [..]
FA01           |    5   |    2    |    0    |    0    |    0   |
FA02           |    0   |    0    |    1    |    0    |    0   |
FA03           |    0   |    0    |    0    |    3    |    0   |
FA04           |    4   |    0    |    1    |    0    |    7   |

等等……按顺序排列的家庭代码和每年每个月的所有值，从较早的月份/年份到现在。可能吗？感谢任何可以提供帮助的人。

Answer 1

如果你想将它用作 View :

SELECT * FROM   
(
    SELECT 
        Concat([Year],'-', [Month]) as [Date],
        ItemFamilyCode,
        TotalShipped
    FROM Shipping -- Or any Table Name
) t 
PIVOT(
    Sum(TotalShipped) 
    FOR [Date] IN (
        [2018-9], 
        [2018-10], 
        [2018-11], 
        [2018-12], 
        [2019-1], 
        [2019-2] -- You have to type all months until today
        )
) AS pivot_table;

并且，动态 sql 如果您可以在 存储过程 中使用它:

将日期列表的内容做成表格，生成日期列表字符串

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';

-- select the category names
SELECT 
    @columns+=QUOTENAME(Date) + ','
FROM 
    DateList
ORDER BY 
    DateList;

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT 
        Concat([Year],'-', [Month]) as [Date],
        ItemFamilyCode,
        TotalShipped 
    FROM Shipping -- Or any Table Name
) t 
PIVOT(
    Sum(TotalShipped)  
    FOR [Date] IN ('+ @columns +')
) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

来源:sqlservertutorial