如果要求未选课的学生记录不必显示出来:declare @sql varchar(8000) set @sql='' select @sql=@sql+',max(case when 课程名name='''+课程名name+''' then 'Y' else null end) as ['+课程名name+']' from 课程表kc group by 课程名nameexec('select 学生ID'+@sql+' from 课程表kc group by 学生ID') 如果要求未选课的学生记录也要显示出来:declare @sql varchar(8000) set @sql='' select @sql=@sql+',max(case when k.课程名name='''+课程名name+''' then 'Y' else null end) as ['+课程名name+']' from 课程表kc group by 课程名nameexec('select s.ID as 学生ID'+@sql+' from 学生表st s left join 课程表kc k on s.ID=k.学生ID group by s.ID')
declare @sql varchar(8000) set @sql = 'select 学生ID,' select @sql = @sql + 'max(case 课程名name when '''+课程名name+''' then source else 0 end) as '''+课程名name+''',' from (select distinct 课程名name from 课程表kc) as a select @sql = left(@sql,len(@sql)-1) + ' from 课程表kc group by name' exec(@sql) go
csdn搜一下,一堆行列转换的例子
ljsql() ( ) 信誉:100 Blog 加为好友 2007-05-25 17:13:42 得分: 0
set @sql=''
select @sql=@sql+',max(case when 课程名name='''+课程名name+''' then 'Y' else null end) as ['+课程名name+']'
from 课程表kc
group by 课程名nameexec('select 学生ID'+@sql+' from 课程表kc group by 学生ID')
如果要求未选课的学生记录也要显示出来:declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when k.课程名name='''+课程名name+''' then 'Y' else null end) as ['+课程名name+']'
from 课程表kc
group by 课程名nameexec('select s.ID as 学生ID'+@sql+' from 学生表st s left join 课程表kc k on s.ID=k.学生ID group by s.ID')
set @sql = 'select 学生ID,'
select @sql = @sql + 'max(case 课程名name when '''+课程名name+'''
then source else 0 end) as '''+课程名name+''','
from (select distinct 课程名name from 课程表kc) as a
select @sql = left(@sql,len(@sql)-1) + ' from 课程表kc group by name'
exec(@sql)
go
表设计不怎么合理,问题也没说清楚看来考官的水平不怎么样,别去了------------------------------------------
汗.....看来我这个公司也呆不下去了
----------------
你出的题?
表设计不怎么合理,问题也没说清楚看来考官的水平不怎么样,别去了------------------------------------------
汗.....看来我这个公司也呆不下去了
----------------
你出的题?-----------------------------------------
有这么巧的事?