经典SQL----行列转换

_id(''tempdb.dbo.#db_scores'') is not null drop table #db_scores
create table #db_scores (sid int,type nvarchar(4),scores int)
insert into #db_scores
select 1,''语文'',80 union all
select 1,''数学'',90 union all
select 2,''语文'',85 union all
select 2,''数学'',90 union all
select 3,''语文'',75 union all
select 3,''数学'',85

declare @sql nvarchar(4000)
set @sql=''select a.sid,a.name,a.sex''
select @sql=@sql+'',max(case when b.type=''''''+type+'''''' then b.scores else 0 end) [''+type+'']''
from (select distinct type from #db_scores) t

exec (@sql+'' from #DB_info a left outer join #db_scores b on a.sid=b.sid group by a.sid,a.name,a.sex'')

/*
sid         name sex  数学          语文
----------- ---- ---- ----------- -----------
1           李明   男    90          80
2           王军   男    90          85
3           李敏   女    85          75

(3 行受影响)
*/