SqlServer生成数据透视表（行列转换等经典SQL语句）

SqlServer⽣成数据透视表（⾏列转换等经典SQL语句）
数据透视表是分析数据的⼀种⽅法，在Excel中就包含了强⼤的数据透视功能。

数据透视是什么样的呢？给个例⼦可能更容易理解。

假设有⼀张数据表：
销售⼈员书籍销量
----------------------------------------
⼩王 Excel教材 10
⼩李 Excel教材 15
⼩王 Word教材 8
⼩李 Excel教材 7
⼩王 Excel教材 9
⼩李 Excel教材 2
⼩王 Word教材 3
⼩李 Excel教材 5
⼀种数据透视的⽅法是统计每个销售⼈员对每种书籍的销量 ,结果如下
----------------------------------------------------------------
Excel教材Word教材总计
---------------------------------------------- -----------------
⼩王 29 0 29
⼩李 19 11 30
各位看明⽩了吗？这是最简单的⼀种数据透视了，如果有必要也可以有多级分组。

好了，那在Sql Server中如何视现数据透视的功能呢？我是Sql Server的初学者，看了⽹上的⼀些例⼦，结合⾃⼰的理解写了下⾯这些Sql语句.
⽣成基础数据的代码
Create table s(
View Code
[name] nvarchar(50),
book nvarchar(50),
saledNumber int
)
insert into s ([name],book,saledNumber) values('⼩王','Excel教材',10);
insert into s ([name],book,saledNumber)values('⼩李','Excel教材',15);
insert into s ([name],book,saledNumber)values('⼩王','Word教材',8);
insert into s ([name],book,saledNumber)values('⼩李','Excel教材',7);
insert into s ([name],book,saledNumber)values('⼩王','Excel教材',9);
insert into s ([name],book,saledNumber)values('⼩李','Excel教材',2);
insert into s ([name],book,saledNumber)values('⼩王','Word教材',3);
insert into s ([name],book,saledNumber)values('⼩李','Excel教材',5);
⽣成数据透视表
View Code
set @sql = 'SELECT [name], '
select @sql = @sql + 'sum(case book when '+quotename(book,'''')+' then saledNumber else 0 end) as ' + quotename(book)+','from s group by book
select @sql = left(@sql,len(@sql)-1)
select @sql = @sql + ', sum(saledNumber) as [sum] from s group by [name]'
select @sql
exec(@sql)
上⾯的查询语句⾸先是拼接了⼀条"Sql语句",它的最终结果为:
SELECT[name], sum(case book when'Excel教材'then saledNumber else0end) as[Excel教材],sum(case book when'Word教
材'then saledNumber else0end) as[Word教材], sum(saledNumber) as[sum]from s group by[name]
当然，如果表中的数据不同，那么这⽣成的Sql语句也是不同的。

最后它调⽤了Sql Server的系统存储过程Exec来执⾏这条语句。

截个图吧。

这就是在Sql Server中⽣成数据透视表的实现，其实它的核⼼也就是上⾯拼接成的那条Sql语句。

更复杂的透视⽅式，⽐如多级透视，也是在这个基础上的实现的。

⾏转列:
在企业应⽤中，经常会开发报表系统，⽽在报表中⼜经常⽤到⾏列转换，这⾥有⼀个经典的应⽤实例：
原表:
姓名科⽬成绩
张三语⽂ 80
张三数学 90
张三物理 85
李四语⽂ 85
李四物理 82
李四英语 90
李四政治 70
王五英语 90
转换后的表：
姓名数学物理英语语⽂政治
李四 0 82 90 85 70
王五 0 0 90 0 0
张三 90 85 0 80 0
实例:
Declare @sql varchar(8000)
Set @sql = 'Select Name as 姓名'
Select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']'
from (select distinct Subject from cj) as cj --把所有唯⼀的科⽬的名称都列举出来
Select @sql = @sql+' from cj group by name'
Exec (@sql)。