--测试
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 行)
*/