是不是这个意思:
--创建测试表
create table subject (id int identity(1,1) primary key, subjectname varchar(20))
go
create table scores (id int identity(1,1) primary key, student varchar(20), subjectid int, score int)
go
--插入测试数据
insert into subject values ('语文')
insert into subject values ('数学')
insert into scores values ('张三', 1, 90)
insert into scores values ('张三', 2, 60)
insert into scores values ('李四', 1, 85)
insert into scores values ('李四', 2, 78)
insert into scores values ('王武', 1, 88)
insert into scores values ('王武', 2, 96)
--横向输出
declare @sql varchar(8000)
set @sql = 'select v.student'
select @sql = @sql + ',isnull(min(case v.subjectid when ' + cast(a.id as varchar(10)) +' then score end),0) ['+subjectname+'] '
from (select id, subjectname from subject) a
select @sql = @sql + 'from scores v group by student'
print @sql
exec (@sql)