行转列问题总结 - 3、行列互转

--测试
if object_id('[tb]') is not null drop table [tb]
go
create table tb(  Student varchar(10),Math int,Class varchar(2), History int)
insert tb values('张三', 90 ,'A1', 90)
insert tb values('李四', 80 ,'A1', 89)
insert tb values('王五', 95 ,'A2', 85)
go

select Factor,max(张三) as 张三 , max(李四) as 李四 , max(王五) as 王五 from
(
  select Factor = 'Math',
  sort=1,
         张三   = case when Student = '张三' then convert(varchar(2),Math) else '' end,
         李四   = case when Student = '李四' then convert(varchar(2),Math) else '' end,
         王五   = case when Student = '王五' then convert(varchar(2),Math) else '' end
  from tb
union all
  select Factor = 'Class' ,
  sort=2,
         张三   = case when Student = '张三' then Class else '' end,
         李四   = case when Student = '李四' then Class else '' end,
         王五   = case when Student = '王五' then Class else '' end
  from tb
union all
  select Factor = 'History' ,
  sort=3, 
         张三   = case when Student = '张三' then convert(varchar(2),History) else '' end,
         李四   = case when Student = '李四' then convert(varchar(2),History) else '' end,
         王五   = case when Student = '王五' then convert(varchar(2),History) else '' end
  from tb
) m
group by Factor,sort
order by sort

--结果
/*
Factor  张三   李四   王五  
------- ---- ---- ----
Math    90   80   95
Class   A1   A1   A2
History 90   89   85

（所影响的行数为 3 行）

*/