SQL pivot 基本用法 行列转换 数据透视

SQL通过pivot进行行列转换 数据透视

可直接在sql server 运行

传统操作 和 pivot

create table XKCl
	(name nchar(10) not null,
	 学科 nchar(10) not null,
	 成绩 int    not null,
	 考试 nchar(10) not null
	 )


insert into  dbo.XKCJ values ('张三','语文',79,'期中')
insert into  dbo.XKCJ values ('李四','语文',85,'期中')
insert into  dbo.XKCJ values ('王五','语文',75,'期中')
insert into  dbo.XKCJ values ('张三','数学',93,'期中')
insert into  dbo.XKCJ values ('李四','数学',98,'期中')
insert into  dbo.XKCJ values ('王五','数学',87,'期中')
insert into  dbo.XKCJ values ('张三','英语',92,'期中')
insert into  dbo.XKCJ values ('李四','英语',90,'期中')
insert into  dbo.XKCJ values ('王五','英语',88,'期中')
insert into  dbo.XKCJ values ('张三','语文',80,'期末')
insert into  dbo.XKCJ values ('李四','语文',81,'期末')
insert into  dbo.XKCJ values ('王五','语文',79,'期末')
insert into  dbo.XKCJ values ('张三','数学',90,'期末')
insert into  dbo.XKCJ values ('李四','数学',95,'期末')
insert into  dbo.XKCJ values ('王五','数学',80,'期末')
insert into  dbo.XKCJ values ('张三','英语',92,'期末')
insert into  dbo.XKCJ values ('李四','英语',90,'期末')
insert into  dbo.XKCJ values ('王五','英语',85,'期末')

SELECT * 
FROM XKCJ

name       学科         成绩          考试
---------- ---------- ----------- ----------------
张三         语文         79          期中
李四         语文         85          期中
王五         语文         75          期中
张三         数学         93          期中
李四         数学         98          期中
王五         数学         87          期中
张三         英语         92          期中
李四         英语         90          期中
王五         英语         88          期中
张三         语文         80          期末
李四         语文         81          期末
王五         语文         79          期末
张三         数学         90          期末
李四         数学         95          期末
王五         数学         80          期末
张三         英语         92          期末
李四         英语         90          期末
王五         英语         85          期末

计算两次考试每名学生各科的最高成绩

select name,
	max(case 学科 when '数学' then 成绩 else 0 end )as 数学,
	max(case 学科 when '英语' then 成绩 else 0 end )as 英语,
	max(case 学科 when '语文' then 成绩 else 0 end )as 语文
From dbo.XKCJ
	group by name

SELECT name,
	max(数学) 数学,
	max(英语) 英语,
	max(语文) 语文
FROM dbo.XKCJ 
	pivot(max(成绩) FOR 学科 IN (语文,数学,英语)) tbnewtb
	group by name 

SELECT *
FROM (select name,学科,成绩
	  from  dbo.XKCJ) new
pivot(max(成绩) FOR 学科 IN (数学,英语,语文)) newtb
	
	
name       数学          英语          语文
---------- ----------- ----------- -----------
李四         98          90          85
王五         87          88          79
张三         93          92          80

计算两次考试各科的最高成绩

select 考试 ,
	max(case 学科 when '数学' then 成绩 else 0 end )as 数学,
	max(case 学科 when '英语' then 成绩 else 0 end )as 英语,
	max(case 学科 when '语文' then 成绩 else 0 end )as 语文
From dbo.XKCJ
	group by 考试 

SELECT *
FROM (select 考试,学科,成绩
	  from  dbo.XKCJ) new
pivot(max(成绩) FOR 学科 IN (数学,英语,语文)) newtb	

	
考试          数学         英语       语文
---------- ----------- ----------- -----------
期末           95          92          81
期中           98          92          85

行列转换

select name,考试,
	sum(case 学科 when '数学' then 成绩 else 0 end )as 数学,
	sum(case 学科 when '英语' then 成绩 else 0 end )as 英语,
	sum(case 学科 when '语文' then 成绩 else 0 end )as 语文
From dbo.XKCJ
	group by name,考试

SELECT *
FROM dbo.XKCJ 
pivot(max(成绩) FOR 学科 IN (语文,数学,英语)) newtb

name         考试         语文          数学       英语
---------- -----------  ----------- ----------- -----------
李四         期末          81          95          90
王五         期末          79          80          85
张三         期末          80          90          92
李四         期中          85          98          90
王五         期中          75          87          88
张三         期中          79          93          92

SELECT name,语文,数学,英语
FROM dbo.XKCJ 
pivot(max(成绩) FOR 学科 IN (语文,数学,英语)) newtb
name       语文          数学          英语
---------- ----------- ----------- -----------
李四         81          95          90
王五         79          80          85
张三         80          90          92
李四         85          98          90
王五         75          87          88
张三         79          93          92

计算每个同学每次考试的总分、平均分

select name,考试,
	sum(case 学科 when '数学' then 成绩 else 0 end )as 数学,
	sum(case 学科 when '英语' then 成绩 else 0 end )as 英语,
	sum(case 学科 when '语文' then 成绩 else 0 end )as 语文,
	SUM(成绩) as total,
	AVG(成绩) as 平均
From dbo.XKCJ
	group by name,考试

SELECT *,
		数学+英语+语文 as total,
		(数学+英语+语文)/3 as 平均
FROM dbo.XKCJ 
pivot(max(成绩) FOR 学科 IN (数学,英语,语文)) newtb

name       考试         数学          英语         语文      total      平均
--------  ---------- ----------- ----------- ----------- ----------- -----------
李四         期末         95          90          81          266         88
王五         期末         80          85          79          244         81
张三         期末         90          92          80          262         87
李四         期中         98          90          85          273         91
王五         期中         87          88          75          250         83
张三         期中         93          92          79          264         88