select intId as id,strName as 姓名, (select strCaption from baseCode where intId=p.intEducationId) as 学历,(select strCaption from basecode where intid= p.intNationId) as 民族
from person p
from person p
from Person a left join BaseCode b on a.intEducationId=b.intId
left join BaseCode c on a.intNationId=c.intId
set @sql=''
declare @Id int
select top 1 @id=intEducationId from Person where intEducationId is not nullselect @id=intParent from Basecode
where intParent<>0 and intId=@idselect @sql=@sql+',isnull(b.strCaption,''未知'') as ['+strCaption+']'
from BaseCode
where intId=@idselect top 1 @id=intNationId from Person where intNationId is not nullselect @id=intParent from Basecode
where intParent<>0 and intId=@idselect @sql=@sql+',isnull(c.strCaption,''未知'') as ['+strCaption+']'
from BaseCode
where intId=@idexec('select a.intId as ID,a.strName as 姓名'+@sql+' from Person a left join BaseCode b on a.intEducationId=b.intId left join BaseCode c on a.intEducationId=c.intId')
姓名 学科 成绩
——————————————————
张三 语文 85
张三 英语 90
张三 数学 95
李四 语文 79
李四 英语 87
李四 数学 90
王五 语文 80
王五 英语 85
王五 数学 70请问如何得到以下格式的查询结果
姓名 语文 英语 数学
———————————————————
张三 85 90 95
李四 79 87 90
王五 80 85 70我是这么写的,可是老是提示 列名 '姓名' 无效。declare @sql varchar(8000)select @sql='select 姓名'
select @sql=@sql+','''+cast('学科' as varchar(10))+'''=sum(case when 学科='+cast('学科' as varchar(10))+' then 成绩 end) '
from (select distinct 学科 from grade) a
select @sql=@sql+' from grade 'group by 姓名exec(@sql)
select @sql=@sql+','''+cast(学科 as varchar(10))+'''=sum(case when 学科='''+cast(学科 as varchar(10))+''' then 成绩 end) '
from (select 'a' 学科 union select 'b' union select 'c' union select 'd') a--select distinct 学科 from grade
select @sql=@sql+' from grade group by 姓名'
exec(@sql)