sql pivot 用法

sql pivot 用法

PIVOT的中文意思是“在枢轴上转动”，比如对于一个2维坐标，将横坐标变成纵坐标，将纵坐标变成横坐标。反映在一个Relational Table上的意思就是：变行为列，变列为行。

练习一

 表一：

studentName 　 Subject 　　Score
张三 　　　　　　 语文 　　    　74
张三 　　　　　　 数学 　　　　 83
张三 　　　　　　 物理 　　　　 93
李四 　　　　　　 语文 　　　　 74
李四 　　　　　　 数学 　　　　 84
李四 　　　　　　 物理 　　　　 94

 

表二：

studentName 　  语文  　　 数学 　　化学　　　物理 　　　　 　　 
张三 　　　　　　 90 　　    110　　  100　　　97
张三 　　　　　　 102　　　 83　　　 95　　　 98

将表一转换成表二：

Select * From StudentScore
PIVOT 
(
  SUM(Score) FOR Subject IN ([语文],[数学],[化学],[物理])
)
AS 
B

SUM(Score) 是我们需要统计的数据，FOR Subject IN ([语文],[数学],[化学],[物理]是统计的范围）就成了我们最终输出的结构了。

这种方式可能出现如下 报错

 

解决方法如下 ，将兼容级别改为 90 或 100 (80是SQL 2000的)

 

 

 

 

练习二

---1、最简单的行转列
/*  

问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94

想变成(得到如下结果)：
姓名 语文 数学 物理
李四 74   84   94
张三 74   83   93
*/
--测试用

IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go

select * from tb

-- 方法一

select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名

 

-- 方法二

--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
( 
   select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
) m,
(
   select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名
) n

where m.姓名 = n.姓名