27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT * FROM [数据源]
AS P
PIVOT
(
SUM([成绩]) FOR
p.[科目]IN ([语文],[数学],[英语])
) AS T
if(OBJECT_ID('tempdb..#test1')is not null) drop table #test1
select * into #test1 from (select '3.5' clas,'t1' name,'v1' project,100 results union all
select '3.5' clas,'t2' name,'v1' project,090 results union all
select '3.5' clas,'t3' name,'v2' project,080 results union all
select '3.6' clas,'t4' name,'v1' project,070 results union all
select '3.6' clas,'t5' name,'v3' project,060 results union all
select '3.6' clas,'t6' name,'v2' project,050 results)as a
select * from #test1
declare @sql1 nvarchar(max);
select @sql1=(select STUFF((SELECT distinct ','+project FROM #test1 FOR XML PATH('')),1,1,''))
select @sql1='Select * from (select clas,project,results from #test1) as a pivot(sum(results) for a.project in ('+@sql1+'))as B'
exec(@sql1)