表名:Info
name questionnum question
8001 1 A
8001 2 B
8001 3 C
8001 4 B
8001 5 A
8001 6 D
8001 7 C
8001 8 B
最终查询结果:
name 1 2 3 4 5 6 7 8
8001 A B C B A D C B
name questionnum question
8001 1 A
8001 2 B
8001 3 C
8001 4 B
8001 5 A
8001 6 D
8001 7 C
8001 8 B
最终查询结果:
name 1 2 3 4 5 6 7 8
8001 A B C B A D C B
http://topic.csdn.net/u/20091013/15/9f058df7-4d29-47bf-a338-b63fcab2abc0.html?11413
pivot
(
max(question)
for questionnum in
([1],[2],[3],[4],[5],[6],[7],[8])
) pvt
set @sql = 'select name as 姓名, sum( case chengji when ''A'' then 1 when ''B'' then 2 when ''C'' then 3 when ''D'' then 4 else 0 end)as 得分'
select @sql = @sql + ' , max(case kemu when ''' + cast(kemu as varchar)+ ''' then chengji else null end) [' + cast(kemu as varchar) + ']'
from (select distinct kemu from Test) as a
set @sql = @sql + ' from Test group by name'
exec(@sql)
set @sql = 'select name as 姓名
select @sql = @sql + ' , max(case questionnum when ''' + cast(questionnum as varchar)+ ''' then question else null end) [' + cast(questionnum as varchar) + ']'
from (select distinct questionnum from info ) as a
set @sql = @sql + ' from info group by name'
exec(@sql)