--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[studentId] varchar(1),
[subject] varchar(4),
[point] int,
[classOrder] int,
[gradeOrder] int
)
insert [test]
select 'A','语文',90,1,2 union all
select 'A','数学',80,4,15 union all
select 'A','英语',95,2,4 union all
select 'B','语文',85,6,25 union all
select 'B','数学',98,1,2 union all
select 'B','英语',100,1,1


declare @str varchar(max)
set @str=''
select  
    @str=@str+','+[subject]+'=max(case when [subject]='+QUOTENAME([subject],'''')
    +' then [point] else 0 end),'
    +'classOrder'+'=max(case when [subject]='+QUOTENAME([subject],'''')
    +' then [classOrder] else 0 end),'
    +'gradeOrder'+'=max(case when [subject]='+QUOTENAME([subject],'''')
    +' then [gradeOrder] else 0 end)'    
from test group by [subject]
exec('select [studentId]'+@str+' from test group by [studentId]')
/*
studentId    数学    classOrder    gradeOrder    英语    classOrder    gradeOrder    语文    classOrder    gradeOrder
-------------------------------------------
A    80    4    15    95    2    4    90    1    2
B    98    1    2    100    1    1    85    6    25
*/

------解决方案--------------------

探讨


除了用动态sql语句外是否可以有其他方法吗，pivot 能否实现多字段的行转列吗

另外存储过程返回数据集能够关联基础表吗？

如像基础表studnet 可以喝sql自定义的表值函数关联
select * from student A
left join dbo.studentPoint(@testId) B on A.studentId = A.studentId


------解决方案--------------------

这个应该不可以吧。你可以把结果集方式图或者临时表里面，再去关联