日期:2014-05-18  浏览次数:20584 次

列转行问题 求SQL解决方案
SQL code

-- 科目表
if object_id('tblSubjectItem') is not null
    drop table tblSubjectItem;
go
create table tblSubjectItem
(
    siID int,
    siName varchar(20)
)
insert into tblSubjectItem(siID, siName)values(1, '语文');
insert into tblSubjectItem(siID, siName)values(2, '数学');
insert into tblSubjectItem(siID, siName)values(3, '物理');
insert into tblSubjectItem(siID, siName)values(4, '英语');
insert into tblSubjectItem(siID, siName)values(5, '化学');
insert into tblSubjectItem(siID, siName)values(6, '生物');
-- 省略其他还有科目
go

-- 科目成绩单
if object_id('tblScoreResult') is not null
    drop table tblScoreResult
go
create table tblScoreResult
(
   [Name]    varchar(10) ,
   subjectID int ,
   Score  int                -- 0 白卷; -1 or null 缺考
)

insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 1 , 74)
insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 2 , 83)
insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 3 , 93)
insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 4 , 0)
insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 5 , -1)
insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 6 , 88)

insert into tblScoreResult([Name] , subjectID , Score) values('李四' , 1 , 74)
insert into tblScoreResult([Name] , subjectID , Score) values('李四' , 2 , 84)
insert into tblScoreResult([Name] , subjectID , Score) values('李四' , 3 , 94)
go

/*
要求返回如下格式结果集
姓名         语文        数学        物理        英语        化学        生物     ...      平均分    总分
---------- ----------- ----------- ----------- ----------- ----------- -----------         ------- -------
李四         74          84          94          缺考        缺考        缺考              42        252
张三         74          83          93          白卷        缺考        88                56.33     338

*/



------解决方案--------------------
--行列互转
--1、行换列
if object_id('Class') is not null
drop table Class
Go
Create table Class(
[Student] nvarchar(2),
[Course] nvarchar(2),
[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go

--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='
 
+quotename([Course],'''')+' then [Score] else 0 end)'
from 
Class group by[Course]
--select @s
exec('select [Student]'+@s+' from Class group by [Student]')
--生成静态:
select 
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
Class 
group by [Student]
GO
--动态:

declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
select @s
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')

--生成静态:
select * 
from 
Class 
pivot 
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b

--生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78

(2 行受影响)
*/

go