另一道很有挑战性的SQL语句，想了好久没想到好的办法
userid questionid answer
20211871A
20211872B
20211873F
20211874B
20211875B
20211876A
20260801A
20260802A
20260803E
20260804A
20260805B
20260806B
20313491B
20313492B
20313493K
20313494A
20313495D
20313496A
转成以下方式输出：
userid question1 question2 question3 question4 question5 question6
2021187 A B F B B A
2026080 A A E A B B
2031349 B B K A D A
[解决办法]
userid questionid answer
20211871A
20211872B
20211873F
20211874B
20211875B
20211876A
select userid ,
max(case when questionid = 1 then answer end) question1,
max(case when questionid = 2 then answer end) question2,
max(case when questionid = 3 then answer end) question3,
max(case when questionid = 4 then answer end) question4,
max(case when questionid = 5 then answer end) question5,
max(case when questionid = 6 then answer end) question6
from tb
group by userid
[解决办法]
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',[question '+rtrim(questionid)+ ']=max(case questionid when '+rtrim(questionid)+ ' then answer end) '
from (select distinct questionid from 表) t
set @sql= 'select userid '+@sql+ ' from 表 group by userid '
exec(@sql)
[解决办法]
create table ta(userid int, questionid int ,answer varchar(5))
insert ta select 2021187,1, 'A '
insert ta select 2021187,2, 'B '
insert ta select 2021187,3, 'F '
insert ta select 2021187,4, 'B '
insert ta select 2021187,5, 'B '
insert ta select 2021187,6, 'A '
insert ta select 2026080,1, 'A '
insert ta select 2026080,2, 'A '
insert ta select 2026080,3, 'E '
insert ta select 2026080,4, 'A '
insert ta select 2026080,5, 'B '
insert ta select 2026080,6, 'B '
insert ta select 2031349,1, 'B '
insert ta select 2031349,2, 'B '
insert ta select 2031349,3, 'K '
insert ta select 2031349,4, 'A '
insert ta select 2031349,5, 'D '
insert ta select 2031349,6, 'A '
declare @s varchar(4000)
set @s= ' '
select @s=@s+ ',[questionid '+rtrim(questionid)+ ']=max( case questionid when '+rtrim(questionid)+ ' then answer end) '
from ta group by questionid
--print @s
set @s= 'select userid '+@s+ ' from ta group by userid '
exec(@s)
userid questionid1 questionid2 questionid3 questionid4 questionid5 questionid6
----------- ----------- ----------- ----------- ----------- ----------- -----------
2021187 A B F B B A
2026080 A A E A B B
2031349 B B K A D A
(3 行受影响)
[解决办法]
create table t(userid int,questionid int,answer varchar(4))
insert into t select 2021187,1, 'A '
insert into t select 2021187,2, 'B '
insert into t select 2021187,3, 'F '
insert into t select 2021187,4, 'B '
insert into t select 2021187,5, 'B '
insert into t select 2021187,6, 'A '
insert into t select 2026080,1, 'A '
insert into t select 2026080,2, 'A '
insert into t select 2026080,3, 'E '
insert into t select 2026080,4, 'A '
insert into t select 2026080,5, 'B '
insert into t select 2026080,6, 'B '
insert into t select 2031349,1, 'B '
insert into t select 2031349,2, 'B '
insert into t select 2031349,3, 'K '
insert into t select 2031349,4, 'A '
insert into t select 2031349,5, 'D '
insert into t select 2031349,6, 'A '
go
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',[question '+rtrim(questionid)+ ']=max(case questionid when '+rtrim(questionid)+ ' then answer end) '
from (select distinct questionid from t) a
set @sql= 'select userid '+@sql+ ' from t group by userid '
exec(@sql)
/*
userid question1 question2 question3 question4 question5 question6
----------- --------- --------- --------- --------- --------- ---------
2021187 A B F B B A
2026080 A A E A B B
2031349 B B K A D A
*/
go
drop table t
go
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(userid varchar(10),questionid int,answer varchar(10))
insert into tb(userid,questionid,answer) values( '2021187 ',1, 'A ')
insert into tb(userid,questionid,answer) values( '2021187 ',2, 'B ')
insert into tb(userid,questionid,answer) values( '2021187 ',3, 'F ')
insert into tb(userid,questionid,answer) values( '2021187 ',4, 'B ')
insert into tb(userid,questionid,answer) values( '2021187 ',5, 'B ')
insert into tb(userid,questionid,answer) values( '2021187 ',6, 'A ')
insert into tb(userid,questionid,answer) values( '2026080 ',1, 'A ')
insert into tb(userid,questionid,answer) values( '2026080 ', 2, 'A ')
insert into tb(userid,questionid,answer) values( '2026080 ',3, 'E ')
insert into tb(userid,questionid,answer) values( '2026080 ',4, 'A ')
insert into tb(userid,questionid,answer) values( '2026080 ',5, 'B ')
insert into tb(userid,questionid,answer) values( '2026080 ',6, 'B ')
insert into tb(userid,questionid,answer) values( '2031349 ',1, 'B ')
insert into tb(userid,questionid,answer) values( '2031349 ',2, 'B ')
insert into tb(userid,questionid,answer) values( '2031349 ',3, 'K ')
insert into tb(userid,questionid,answer) values( '2031349 ',4, 'A ')
insert into tb(userid,questionid,answer) values( '2031349 ',5, 'D ')
insert into tb(userid,questionid,answer) values( '2031349 ',6, 'A ')
go
select userid ,
max(case when questionid = 1 then answer end) question1,
max(case when questionid = 2 then answer end) question2,
max(case when questionid = 3 then answer end) question3,
max(case when questionid = 4 then answer end) question4,
max(case when questionid = 5 then answer end) question5,
max(case when questionid = 6 then answer end) question6
from tb
group by userid
drop table tb
/*
userid question1 question2 question3 question4 question5 question6
---------- ---------- ---------- ---------- ---------- ---------- ----------
2021187 A B F B B A
2026080 A A E A B B
2031349 B B K A D A
（所影响的行数为 3 行）
*/
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(userid varchar(10),questionid int,answer varchar(10))
insert into tb(userid,questionid,answer) values( '2021187 ',1, 'A ')
insert into tb(userid,questionid,answer) values( '2021187 ',2, 'B ')
insert into tb(userid,questionid,answer) values( '2021187 ',3, 'F ')
insert into tb(userid,questionid,answer) values( '2021187 ',4, 'B ')
insert into tb(userid,questionid,answer) values( '2021187 ',5, 'B ')
insert into tb(userid,questionid,answer) values( '2021187 ',6, 'A ')
insert into tb(userid,questionid,answer) values( '2026080 ',1, 'A ')
insert into tb(userid,questionid,answer) values( '2026080 ', 2, 'A ')
insert into tb(userid,questionid,answer) values( '2026080 ',3, 'E ')
insert into tb(userid,questionid,answer) values( '2026080 ',4, 'A ')
insert into tb(userid,questionid,answer) values( '2026080 ',5, 'B ')
insert into tb(userid,questionid,answer) values( '2026080 ',6, 'B ')
insert into tb(userid,questionid,answer) values( '2031349 ',1, 'B ')
insert into tb(userid,questionid,answer) values( '2031349 ',2, 'B ')
insert into tb(userid,questionid,answer) values( '2031349 ',3, 'K ')
insert into tb(userid,questionid,answer) values( '2031349 ',4, 'A ')
insert into tb(userid,questionid,answer) values( '2031349 ',5, 'D ')
insert into tb(userid,questionid,answer) values( '2031349 ',6, 'A ')
go
declare @sql varchar(8000)
set @sql = 'select userid '
select @sql = @sql + ' , max(case questionid when ' ' ' + cast(questionid as varchar) + ' ' ' then answer end) [ ' + 'question ' + cast(questionid as varchar) + '] '
from (select distinct questionid from tb) as a
set @sql = @sql + ' from tb group by userid '
exec(@sql)
drop table tb
/*
userid question1 question2 question3 question4 question5 question6
---------- ---------- ---------- ---------- ---------- ---------- ----------
2021187 A B F B B A
2026080 A A E A B B
2031349 B B K A D A
*/
[解决办法]
-- in sql 2005
SELECT *
FROM table AS ATR
PIVOT
(
MAX(answer)
FOR questionid IN([1], [2], [3], [4], [5],[6])
) AS PVT
