sql学习~pivot和unpivot用法

Vincent-yuan 2020-03-31 原文

pivot

可以把列值转换为输出中的多个列。

pivot 可以在其他剩余的列的值上执行聚合函数。

unpivot

将列转换为列值

语法

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

示例1：pivot

1.数据准备

create table student_score
(
    studentId varchar(50),
    subjectName varchar(50),
    score decimal(18)
)

insert into student_score values
('001','语文',80),('001','数学',70),('001','英语',90),
('002','语文',80),('002','数学',83),('002','英语',60),
('003','语文',50),('003','数学',90),('003','英语',60),
('004','语文',90),('004','数学',80)

按学生id分组查看平均成绩

select studentId,AVG(score) avgScore from student_score
group by studentId

初始效果

2.使用pivot

select 'averagescore' as avgScore_by_studentId,
[001],[002],[003],[004]
from 
(
select studentId,score 
    from student_score
) as sourceTable
pivot
(
AVG(score) for studentId in ([001],[002],[003],[004])
) as pivotTable

3.效果

示例2：unpivot

1.数据准备

-- Create the table and insert values as portrayed in the previous example.  
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,  
    Emp3 int, Emp4 int, Emp5 int);  
GO  
INSERT INTO pvt VALUES (1,4,3,5,4,4);  
INSERT INTO pvt VALUES (2,4,1,5,5,5);  
INSERT INTO pvt VALUES (3,4,3,5,4,4);  
INSERT INTO pvt VALUES (4,4,2,5,5,4);  
INSERT INTO pvt VALUES (5,5,1,5,5,5);  
GO

初始效果

表示供应商(vendorID)在用户1(Emp1)中的订单数量，其他类比即可。

2.使用示例

-- Unpivot the table.  
SELECT VendorID, Employee, Orders  
FROM   
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5  
   FROM pvt) p  
UNPIVOT  
   (Orders FOR Employee IN   
      (Emp1, Emp2, Emp3, Emp4, Emp5)  
)AS unpvt;