展开全部
举例说明32313133353236313431303231363533e58685e5aeb931333337396237怎么用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)
执行效果图:
(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)
执行效果图: