use master
go
if exists(select name from sys.databases where name='Data')
drop database Data
go
create database Data
go
use Data
go
if exists(select name from sys.objects where name='studentOne')
drop table studentOne
go
create table studentOne
(
studentName nvarchar( 10 ),
studentSubject nvarchar( 10 ),
result int
)
go
insert into studentOne(studentName,studentSubject,result) values(' 张三' , ' 语文' , 80)
insert into studentOne(studentName,studentSubject,result) values(' 张三' , ' 数学' , 90 )
insert into studentOne(studentName,studentSubject,result) values(' 张三' , ' 物理' , 85)
insert into studentOne(studentName,studentSubject,result) values( ' 李四' , ' 语文' , 85)
insert into studentOne(studentName,studentSubject,result) values(' 李四' , ' 数学' , 92 )
insert into studentOne(studentName,studentSubject,result) values( ' 李四' , ' 物理' , 82)
insert into studentOne(studentName,studentSubject,result) values(' 李四' , ' 化学' , 82)
go
select * from studentOne
go
---------------------------
--行转列
---------------------------
--步骤一：
select studentName from studentOne group by studentName
--步骤二：
select studentName,max(case when studentSubject='语文' then result end) as '语文' from studentOne group by studentName
--步骤三：
select studentName,isnull(sum(case when studentSubject='语文' then result end),0) as '语文' from studentOne group by studentName
--解释：
/*
按照上面的三个步骤分别看一下是什么结果
解释第二句：按照语文这个条件找，找到相对应的分数
可以看出值都是null这个时候就要用到isnull将值进行转换(转成)
*/
declare @sql varchar(4000)
set @sql=' select studentName '
select @sql= @sql+' ,isnull(max(case studentSubject when ''' +studentSubject+ ''' then result end),0) ['+studentSubject+']'
from (select distinct studentSubject from studentOne) as temp
select @sql=@sql+' from studentOne group by studentName'
print @sql
exec (@sql)
---------------------------
--列转行
---------------------------
if exists(select name from sys.objects where name='studentTwo')
drop table studentTwo
go
create table studentTwo
(
studentName nvarchar( 10 ),
化学 int ,
数学 int ,
物理 int ,
语文 int
)
go
insert into studentTwo(studentName,化学,数学,物理,语文) values(' 李四' , 82 , 92 , 82 , 85)
insert into studentTwo(studentName,化学,数学,物理,语文) values(' 张三' , 0 , 90 , 85 , 80 )
go
select * from studentTwo
go
select '李四' as studentName,studentName='化学',化学 as result from studentTwo where studentName='李四'
union all
select '李四' as studentName,studentName='数学',数学 as result from studentTwo where studentName='李四'
union all
select '李四' as studentName,studentName='物理',物理 as result from studentTwo where studentName='李四'
union all
select '李四' as studentName,studentName='语文',语文 as result from studentTwo where studentName='李四'
union all
select '张三' as studentName,studentName='化学',化学 as result from studentTwo where studentName='张三'
union all
select '张三' as studentName,studentName='数学',数学 as result from studentTwo where studentName='张三'
union all
select '张三' as studentName,studentName='物理',物理 as result from studentTwo where studentName='张三'
union all
select '张三' as studentName,studentName='语文',语文 as result from studentTwo where studentName='张三'
go
declare @sql varchar(4000)
set @sql=''
select @sql=@sql+' select '''+studentName+''' as studentName,studentName=''化学'',化学as result from studentTwo where studentName='''+studentName+'''
union all
select '''+studentName+''' as studentName,studentName=''数学'',数学as result from studentTwo where studentName='''+studentName+'''
union all
select '''+studentName+''' as studentName,studentName=''物理'',物理as result from studentTwo where studentName='''+studentName+'''
union all
select '''+studentName+''' as studentName,studentName=''语文'',语文as result from studentTwo where studentName='''+studentName+'''
union all'
from (select studentName from studentTwo) as temp
select @sql=left(@sql,len(@sql)-10)
print @sql
execute (@sql)