列转行问题 求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 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)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)