列转行有关问题求SQL解决方案

2012-03-24

列转行问题求SQL解决方案SQL code-- 科目表if object_id(tblSubjectItem) is not nulldrop table tblSu

列转行问题求SQL解决方案

SQL code

-- 科目表if object_id('tblSubjectItem') is not null    drop table tblSubjectItem;gocreate 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 tblScoreResultgocreate 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
--加上总成绩(学科平均分)

--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]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))

生成动态:

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),
[总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]

go

--2005方法:

动态:

declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
--isnull(@s+',','') 去掉字符串@s中第一个逗号

exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')

--生成静态:

select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:

/*
Student 数学物理英语语文总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337

(2 行受影响)
*/

go

--2、列转行

if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go

--2000:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序

生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]

go
--2005:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')

go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四数学 77
李四物理 85
李四英语 65
李四语文 65
张三数学 87
张三物理 90
张三英语 82
张三语文 78
*/

[解决办法]
精华帖里有行列转换经典方法，LZ自己搜搜吧
[解决办法]

探讨
复制和粘贴谁多会啊？不过还是非常感谢你的关注！

[解决办法]
说个解决的方法，把结果转成行形式的，然后将两个表的数据拼成这种形式，再根据一楼的方法行转列。

[解决办法]

SQL code

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, '生物');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)godeclare @sql varchar(8000)set @sql = 'select m.name 姓名 'select @sql = @sql + ' , max(case m.subjectid when ' + cast(siid as varchar) + ' then m.score else -1 end) [' + siname + ']'from (select distinct siid , siname from tblSubjectItem) as aset @sql = @sql + ',cast(avg(m.score) as decimal(18,2)) 平均分 , sum(m.score) 总分 from tblScoreResult m group by m.name'exec(@sql) drop table tblSubjectItem , tblScoreResult/*姓名         语文          数学          物理          英语          化学          生物          平均分                  总分          ---------- ----------- ----------- ----------- ----------- ----------- ----------- -------------------- ----------- 李四         74          84          94          -1          -1          -1          84.00                252张三         74          83          93          0           -1          88          56.00                337*/
[解决办法]
应该可以用case when else end判断一下
[解决办法]
解决就速度结贴
[解决办法]
SQL codecreate 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, '生物');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)declare @sql varchar(6000)select @sql='with t as(select c.name,c.siname,isnull(d.score,-1) scorefrom(select a.name,b.siname,b.siID from(select distinct Name from tblScoreResult) across join tblSubjectItem b) cleft join tblScoreResult d on c.name=d.name and c.siID=d.subjectID)select name,'select @sql=@sql+'(select case when score=-1 then ''缺考'' when score=0 then ''白卷'' else cast(score as varchar(3)) end from t t2 where t2.name=t.name and t2.siname='''+siName+''') '''+siName+''','from tblSubjectItemselect @sql=@sql+'sum(case when score=-1 then 0 else score end)/(select count(*) from tblSubjectItem) ''平均分'','+'sum(case when score=-1 then 0 else score end) ''总分'' from t group by name 'exec(@sql)name       语文  数学  物理  英语  化学  生物   平均分         总分---------- ---- ---- ---- ---- ---- ---- ----------- -----------李四         74   84   94   缺考   缺考   缺考   42          252张三         74   83   93   白卷   缺考   88    56          338(2 row(s) affected)

热点排行

SQL Server

列转行有关问题 求SQL解决方案

列转行有关问题求SQL解决方案