create table tb
(
[答卷] varchar(10),
[题目] varchar(10),
[答案] varchar(10)
)
insert tb
select '答卷1','题目1','答案1' union
select '答卷1','题目2','答案2' union
select '答卷1','题目3','答案3' union
select '答卷2','题目1','答案4' union
select '答卷2','题目2','答案5' union
select '答卷2','题目3','答案6'
--查询
declare @sql varchar(8000),
@i int
select @i=isnull(@i,0)+1
,@sql=isnull(@sql,'')+',[答卷'+convert(varchar,@i)+']=max(case when [答卷]=''答卷'+convert(varchar,@i)+''' then [答案] else null end)'
from tb
group by [答卷]
select @sql='select 题目'+@sql+' from tb group by 题目'
exec(@sql)
--创建测试环境
create table 表 (答卷 varchar(10),题目 varchar(10),答案 varchar(10))
insert into 表 select '答卷1','题目1','答案1'
union all select '答卷1','题目2','答案2'
union all select '答卷1','题目3','答案3'
union all select '答卷2','题目1','答案4'
union all select '答卷2','题目2','答案5'
union all select '答卷2','题目3','答案6'
--动态SQL
declare @s varchar(2000)
set @s='select 题目 '
select @s=@s+',['+答卷+']=max(case when 答卷='''+答卷+''' then 答案 end )'
from 表
group by 答卷
set @s=@s+' from 表 Group by 题目'
exec (@s)
declare @sql varchar(8000),
@i int
select @i=isnull(@i,0)+1
,@sql=isnull(@sql,'')+',[答卷'+convert(varchar,@i)+']=max(case when [答卷]=''答卷'+convert(varchar,@i)+''' then [答案'+convert(varchar,@i)+'] else 0 end)'
from 表
group by 答卷
select @sql='select 题目'+@sql+' from 表 group by 题目'
exec(@sql)
declare @sql varchar(8000),
@i int
select @sql=isnull(@sql,'')+',[答卷'+convert(varchar,@i)+']=max(case when [答卷]=''答卷'+convert(varchar,@i)+''' then [答案'+convert(varchar,@i)+'] else 0 end)'
,@i=isnull(@i,0)+1
from 表
group by 答卷
select @sql='select 题目'+@sql+' from 表 group by 题目'
exec(@sql)
declare @sql varchar(8000),
@i int
select @sql=isnull(@sql,'')+',[答卷'+convert(varchar,@i)+']=max(case when [答卷]=''答卷'+convert(varchar,@i)+''' then [答案'+convert(varchar,@i)+'] else 0 end)'
,@i=@i+1
from 表
group by 答卷
select @sql='select 题目'+@sql+' from 表 group by 题目'
exec(@sql)