返回目录：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中如何视现数据透视的功能呢zhidao？我是Sql Server的初学者，看了网上的一些例子，结合自己的理解写了下面这些Sql语句.
给个例子可能更容易理解，假设有一张数据表：
销售人员 书籍 销量
----------------------------------------
小王 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语句.
生成基础数据的e799bee5baa6e997aee7ad94e78988e69d83339代码
Create table s( [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);
生成数据透视表
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 else 0 end) as [Excel教材],sum(case book when 'Word教材' then saledNumber else 0 end) as [Word教材], sum(saledNumber) as [sum] from s group by [name]
当然，如果表中的数据不同，那么这生成的Sql语句也是不同的。
最后调用了Sql Server的系统存储过程Exec来执行这条语句。
这就是在Sql Server中生成数据透视表的实现，核心也就是上面拼接成的那条Sql语句。更复杂的透视方式，比如多级透视，也是在这个基础上的实现的。
直接在excel里获取外部数据，可以直接显示成数据透视表
举例说明怎么用SQL做数据透视表：
[表一 学生信息表]
[表二 课程信息表]
[表三 成绩信息表]
生成基础数据的代码
CREATE TABLE [dbo].[StuInfo] (
[StuID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[StuName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CourseInfo] (
[CourseID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CourseName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ScoreInfo] (
[StuID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CourseID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Score] [float] NULL
) ON [PRIMARY]
GO
insert stuInfo(StuID,StuName) values ('090301','张三')
insert stuInfo(StuID,StuName) values ('090302','李四')
insert stuInfo(StuID,StuName) values ('090303','王五')
insert CourseInfo(CourseID,CourseName) values ('201001','数学')
insert CourseInfo(CourseID,CourseName) values ('201002','C语言')
insert ScoreInfo(StuID,CourseID,Score) values ('090301','201001',90)
insert ScoreInfo(StuID,CourseID,Score) values ('090301','201002',100)
insert ScoreInfo(StuID,CourseID,Score) values ('090302','201001',95)
insert ScoreInfo(StuID,CourseID,Score) values ('090302','201002',98)
insert ScoreInfo(StuID,CourseID,Score) values ('090303','201001',88)
生成数据透视表
(1)单表 ScoreInfo
declare @sql nvarchar(500)
set @sql = 'SELECT [StuID], '
select @sql = @sql + 'sum(case CourseID when '+quotename(CourseID,'''')+' then score else 0 end) as ' + quotename(CourseID)+',' from ScoreInfo group by CourseID
select @sql = left(@sql,len(@sql)-1)
select @sql = @sql + ', sum(score) as [sum] from ScoreInfo group by [StuID]'
select @sql
exec(@sql)
执行效果图：7a64e58685e5aeb9337
(2)双表 StuInfo,ScoreInfo
declare @sql nvarchar(500)
set @sql = 'SELECT (select stuName from StuInfo where StuInfo.stuID=scoreInfo.stuID), '
select @sql = @sql + 'sum(case CourseID when '+quotename(CourseID,'''')+' then score else 0 end) as ' + quotename(CourseID)+',' from ScoreInfo group by CourseID
select @sql = left(@sql,len(@sql)-1)
select @sql = @sql + ', sum(score) as [sum] from ScoreInfo group by [StuID]'
select @sql
exec(@sql)
执行效果图：
(3)多表 StuInfo,ScoreInfo,ScoreInfo
declare @sql nvarchar(500)
set @sql = 'SELECT (select stuName from StuInfo where StuInfo.stuID=scoreInfo.stuID) as stuName, '
select @sql = @sql + 'sum(case CourseID when '+quotename(CourseID,'''')+' then score else 0 end) as ' + quotename((select courseName from courseInfo where
courseInfo.CourseID=ScoreInfo.CourseID),'''')+',' from ScoreInfo group by CourseID
select @sql = left(@sql,len(@sql)-1)
select @sql = @sql + ', sum(score) as [sum] from ScoreInfo group by [StuID]'
select @sql
exec(@sql)
执行效果图：