列转行有关问题 求SQL解决方案
日期：2014-05-18 浏览次数：20569 次
列转行问题 求SQL解决方案
------解决方案--------------------
--行列互转
--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
- 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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
