SQL 使用SQL Server的PIVOT操作符创建交叉报表

关于使用SQL Server的PIVOT操作符创建交叉报表的完整攻略，我会分成以下几个步骤来介绍：

1. 确定数据源
2. 定义PIVOT表达式
3. 编写PIVOT查询语句

下面我就具体讲解一下这三个步骤。

1.确定数据源

在使用PIVOT操作符创建交叉报表之前，我们需要先确定一个数据源。碰巧我的电脑上有一个名叫“Sales”的数据库，它有一张名叫“Orders”的表，我们可以以该数据源为例进行演示。

2.定义PIVOT表达式

定义PIVOT表达式通常需要包含以下几个部分：

• 聚合函数：指定要使用的聚合函数，例如SUM、AVG、MAX等。
• 要使用的列：指定要在列中显示的列，通常是数据源表中的一列。
• 需要转换为列的列：指定要将哪些列转换为列。

在Sale数据库的Orders表中，我们可以使用以下PIVOT表达式：

SELECT *
FROM (
    SELECT 
        Country, 
        YEAR(OrderDate) AS OrderYear, 
        Product,
        TotalAmount
    FROM Orders
) AS SourceTable
PIVOT (
    SUM(TotalAmount)
    FOR Product IN([A], [B], [C])
) AS PivotTable;

在这个PIVOT表达式中，我们使用了SUM聚合函数，将TotalAmount列作为要在列中显示的列，将Product列转换为列。这个表达式还包含了一个子查询，它从原始数据源表中选择了Country、OrderDate和TotalAmount三列，并将OrderDate转换为OrderYear。

3.编写PIVOT查询语句

有了PIVOT表达式后，我们就可以在SQL Server中使用该表达式来编写PIVOT查询语句了，具体方法如下：

SELECT *
FROM (
    SELECT 
        Country, 
        YEAR(OrderDate) AS OrderYear, 
        Product,
        TotalAmount
    FROM Orders
) AS SourceTable
PIVOT (
    SUM(TotalAmount)
    FOR Product IN([A], [B], [C])
) AS PivotTable;

这条语句的作用是将Sales数据库中的Orders表转换为一个交叉报表，该报表将年份作为行，将产品作为列，并且按照国家和产品销售额进行了汇总。这样我们就可以非常直观地查看每个产品在每个年份以及各国的销售额。

下面我再来举一些实例。

假设我们有如下销售数据：

Country	Product	Year	Sales
USA	A	2019	100
USA	A	2020	120
USA	A	2021	90
USA	B	2019	80
USA	B	2020	100
USA	B	2021	110
USA	C	2019	50
USA	C	2020	70
USA	C	2021	75
China	A	2019	80
China	A	2020	70
China	A	2021	95
China	B	2019	60
China	B	2020	90
China	B	2021	50
China	C	2019	40
China	C	2020	55
China	C	2021	60

我们可以使用以下SQL语句将这个数据源转换为交叉报表：

SELECT *
FROM (
    SELECT 
        Country, 
        YEAR(OrderDate) AS OrderYear, 
        Product,
        TotalAmount
    FROM Orders
) AS SourceTable
PIVOT (
    SUM(TotalAmount)
    FOR Product IN ([A], [B], [C])
) AS PivotTable;

这个语句的执行结果为：

Country	OrderYear	A	B	C
China	2019	80	60	40
China	2020	70	90	55
China	2021	95	50	60
USA	2019	100	80	50
USA	2020	120	100	70
USA	2021	90	110	75

我们还可以使用PIVOT操作符实现更加复杂的交叉报表，例如我们可以对每个国家的不同产品类型按照月份进行汇总，具体实现方法如下：

SELECT *
FROM (
    SELECT 
        Country, 
        Product,
        YEAR(OrderDate) AS OrderYear,
        MONTH(OrderDate) AS OrderMonth,
        TotalAmount
    FROM Orders
) AS SourceTable
PIVOT (
    SUM(TotalAmount)
    FOR OrderMonth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS PivotTable;

这个语句的执行结果为：

Country	Product	OrderYear	1	2	3	4	5	6	7	8	9	10	11	12
China	A	2019	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
China	A	2020	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
China	A	2021	95	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
China	B	2019	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
China	B	2020	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
China	B	2021	50	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
China	C	2019	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
China	C	2020	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
China	C	2021	60	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
USA	A	2019	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
USA	A	2020	120	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
USA	A	2021	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	90	NULL	NULL	NULL
USA	B	2019	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
USA	B	2020	NULL	100	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
USA	B	2021	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	110	NULL	NULL
USA	C	2019	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
USA	C	2020	NULL	NULL	70	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
USA	C	2021	NULL	NULL	NULL	NULL	NULL	NULL	75	NULL	NULL	NULL	NULL	NULL

这个报表展示了每个国家的每个产品在每个月份的销售额，并用NULL填充了缺失的数据。

总结：使用SQL Server的PIVOT操作符可以轻松地将原始数据源转换为交叉报表，通过对PIVOT表达式和查询语句的灵活应用，我们可以创建不同维度、不同复杂度的报表，从而更好地理解和分析我们的数据。