的功能MSSQL中利用Pivot实现数据转换的初探

1. 什么是pivot

Pivot是SQL Server的一个重要功能，它可以将列（Column）转化为行（Row），将行转换为列。该功能使得数据提取和报表生成变得更加方便和易于理解。很多时候，pivot是处理类别数据的最佳工具，它可以将行的值转换为列，并统计每列值的数量，从而更加直观地展示数据。

2. Pivot的语法

下面是Pivot语法的基本格式：

SELECT [非聚合列],[Pivot列1], [Pivot列2],…,[Pivot列n]

FROM table

PIVOT (AggregateFunction（聚合函数）([聚合列])

FOR Pivot列

IN ([Pivot值1],[Pivot值2],…,[Pivot值n])) P

其中：

非聚合列：代表需要展示的列名，与Pivot列不同，其值作为每行的分组依据。

Pivot列：需要进行转置的列，其值将在输出结果中作为列名。

聚合函数：对每个新列进行聚合操作的函数，如SUM、AVG、COUNT等。

聚合列：需要聚合的列名。

3. Pivot的应用举例

3.1 成绩单数据的Pivot

考虑下面这个成绩单数据表：

CREATE TABLE Scores (
    StudentID int,
    Subject varchar(20),
    Score float
);
INSERT INTO Scores VALUES (1, 'Math', 89);
INSERT INTO Scores VALUES (1, 'English', 90);
INSERT INTO Scores VALUES (1, 'Science', 75);
INSERT INTO Scores VALUES (2, 'Math', 95);
INSERT INTO Scores VALUES (2, 'English', 82);
INSERT INTO Scores VALUES (2, 'Science', 87);
INSERT INTO Scores VALUES (3, 'Math', 97);
INSERT INTO Scores VALUES (3, 'English', 85);
INSERT INTO Scores VALUES (3, 'Science', 91);

如果需要将这张成绩单数据表按照课程转置为如下形式：

Subject

Mary

Bob

John
Math

89

95

97
English

90

82

85
Science

75

87

91

可以这样写SQL代码：

SELECT Subject, Mary, Bob, John 
FROM (
    SELECT 
        StudentID, 
        Subject, 
        Score 
    FROM Scores
) AS sourceTable
PIVOT (
    AVG(Score)
    FOR StudentID IN (Mary, Bob, John)
) AS pivotTable;

在该语句中，我们先选出需要的三列数据(StudentID, Subject, Score)，然后对Score列应用聚合函数(AVG)，最后将StudentID列进行Pivot操作。在Pivot操作中，每个学生的列名被作为Pivot列的值，而分数成为了每个学生对应列的聚合值。

3.2 多列数据的Pivot

当需要对多列数据进行操作时，需要利用UNPIVOT和PIVOT两个操作。

下面我们来考虑一个例题：给定下面这个数据表：

CREATE TABLE T1(
    id int,
    product varchar(10),
    quarter1 float,
    quarter2 float,
    quarter3 float,
    quarter4 float
);
INSERT INTO T1 VALUES (1, 'A', 23.4, 45.6, 57.8, 89.4);
INSERT INTO T1 VALUES (2, 'B', 45.6, 12.3, 65.4, 89.9);
INSERT INTO T1 VALUES (3, 'C', 90.9, 78.6, 56.8, 87.6);

我们希望将它展示成如下格式：

id

product

Qtr

Qtr_Values


1

A

quarter1

23.4


1

A

quarter2

45.6


1

A

quarter3

57.8


1

A

quarter4

89.4


2

B

quarter1

45.6


2

B

quarter2

12.3


2

B

quarter3

65.4


2

B

quarter4

89.9


3

C

quarter1

90.9


3

C

quarter2

78.6


3

C

quarter3

56.8


3

C

quarter4

87.6

首先，我们需要使用UNPIVOT操作来转换表的列数据为行数据。

SELECT id, product, Qtr, Qtr_Values 
FROM (
    SELECT 
        id, 
        product, 
        quarter1, 
        quarter2, 
        quarter3, 
        quarter4 
    FROM T1
) as SourceTable 
UNPIVOT (
    Qtr_Values FOR Qtr IN (
        quarter1, 
        quarter2, 
        quarter3, 
        quarter4
    )
) AS unPivotTable;

在UNPIVOT操作中，我们把需要转换的列数据指定为4个列：quarter1, quarter2, quarter3, quarter4，把Qtr_Values设置为转换后输出的数据列名。执行这条SQL语句后，我们得到了不同的行数据（每个产品的每个季度数据作为一行）。

紧接着，我们需要对转换后的行数据实施PIVOT操作，将Qtr列里的值作为列名，储存每个月份的销售数量。具体代码如下：

SELECT * 
FROM (
    SELECT id, product, Qtr, Qtr_Values 
    FROM (
        SELECT 
            id, 
            product, 
            quarter1, 
            quarter2, 
            quarter3, 
            quarter4 
        FROM T1
    ) as SourceTable 
    UNPIVOT (
        Qtr_Values FOR Qtr IN (
            quarter1, 
            quarter2, 
            quarter3, 
            quarter4
        )
    ) AS unPivotTable
) as pTable 
PIVOT (
    MAX(Qtr_Values) 
    FOR Qtr IN (
        quarter1, 
        quarter2, 
        quarter3, 
        quarter4
    )
) AS PivotTable;

执行上述代码，我们就可以再次将数据逆转，得到下面的结果表：

id

product

quarter1

quarter2

quarter3

quarter4
1

A

23.4

45.6

57.8

89.4
2

B

45.6

12.3

65.4

89.9
3

C

90.9

78.6

56.8

87.6

总结

Pivot是一个非常强大的SQL Server功能，通过它我们可以把列变成行，行变成列，这在很多时候是非常有价值的。在本文中，我们通过示例详细讲解了Pivot的语法和应用，相信读者已经能从中获得一些启发和帮助，同时也会体会到Pivot操作的巨大威力。