sql学习~pivot和unpivot⽤法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;
3.效果

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。