Pivot For和UNPivot For

时间:2023-03-08 16:09:35
Pivot For和UNPivot For

一、使用PIVOT和UNPIVOT命令的SQL Server版本要求

1.数据库的最低版本要求为SQL Server 2005 或更高。

2.必须将数据库的兼容级别设置为90 或更高。

3.查看我的数据库版本及兼容级别。

如果不知道怎么看数据库版本或兼容级别的话可以在SQL Server Management Studio新建一个查询窗口输入:print @@version,运行之后在我的本机上得到:

Pivot For和UNPivot For

然后我们选择一个数据库然后右键-属性 选择[选项]得到下图的信息。

Pivot For和UNPivot For

二、使用PIVOT实现列转行

PIVOT语法:

SELECT  <未透视的列>,
[第一个透视列] AS <列别名>,
[第二个透视列] AS <列别名>,
...
[最后一个透视列] AS <列别名>
FROM ( <SELECT查询>
) AS <源表>
PIVOT
( <聚合函数>(<列>)
FOR [<需要转换为行的列>]
IN ( [第一个透视列], [第二个透视列], ... [最后一个透视列] )
) AS <数据透视表> <可选的ORDER BY子句>;

创建临时表学生成绩表#T_Score,包括三个字段(学生姓名(Student_Name),科目(Class_Name),成绩(Score))

创建临时表的sql:

CREATE TABLE #T_Score
(
Student_Name VARCHAR(30),
Class_Name VARCHAR(30),
Score INT
)

向T_Score表中插入6条记录,筛选结果为:

Pivot For和UNPivot For

1、要求,筛选出每个学生语文,数学,英语的成绩

SELECT  Student_Name ,
语文 ,
数学 ,
英语
FROM #T_Score --这里是PIVOT第二步骤(准备原始的查询结果,因为PIVOT是对一个原始的查询结果集进行转换操作,所以先查询一个结果集出来)这里可以是一个select子查询,但为子查询时候要指定别名,否则语法错误
PIVOT ( SUM(Score) FOR Class_Name IN ( [语文], [英语], [数学] ) ) p
--思路:将Class_Name列值转换成列名,转换后的列值为分数
--聚合函数SUM表示你需要怎样处理转换后的列的值,是总和(sum),还是平均(avg)还是min,max等等
--例如如果T_Score表中有两条数据并且其Class_Name都是“语文”且Student_Name都是张三,其中一条的Score是90,另一条Score是100,那么在这里使用sum,行转列后“语文”这个列的值当然是190了
--后面的FOR Class_Name IN ( [语文], [英语], [数学] ...)中 for Class_Name就是说将Class_Name列的值分别转换成一个个列,也就是“以值变列”。但是需要转换成列的值有可能有很多,我们只想取其中几个值转换成列,那么怎样取呢?就是在in里面了,比如我此刻每个学生语文和数学的成绩,在in里面就只写“语文”和“数学”(注意,in里面是原来week列的值,"以值变列")。
--总的来说,SUM(Score) FOR Class_Name IN ( [语文], [英语], [数学] )
--这句的意思如果直译出来,就是说:将列Class_Name值为"[语文], [英语], [数学]分别转换成列,这些列的值取Score的总和。

结果:Pivot For和UNPivot For

2、比第一个问题更加深一步,要求,筛选出每个同学语文,数据,英语的成绩,并计算每个学生的总成绩

SELECT  Student_Name , 语文 ,数学 ,英语 ,语文 + 数学 + 英语 AS 总成绩
FROM #T_Score PIVOT ( SUM(Score) FOR Class_Name IN ( 语文, 数学, 英语 ) ) p

结果:Pivot For和UNPivot For

二、使用UNPIVOT实现行转列

下面用实例来说明:

创建临时表#T_ClassScore,该表中包含四个字段,分别是学生姓名,语文、数学、英语的成绩

创建临时表的sql:

CREATE TABLE #T_ClassScore
(
学生姓名 VARCHAR(30),
语文 INT,
数学 INT,
英语 INT
)

向临时表中出入两条数据,结果为:

Pivot For和UNPivot For

UnPivot for 的用法

SELECT * FROM #T_ClassScore
SELECT 学生姓名 ,
课程 ,
分数
FROM #T_ClassScore UNPIVOT( 分数 FOR 课程 IN ( 语文, 数学, 英语 ) ) p
--将语文,数学,英语列名改成列值,列名为课程。那么语文,数学,英语的原列值怎么办?取名为分数