SQL 行变列 列变行
标签：val nbsp sel else char create name group 插入
SQL 行变列 列变行
--创建Table
create table student(name varchar(50),course varchar(50),score int)
insert into student values(‘張三‘,‘語文‘,80)
insert into student values(‘張三‘,‘數學‘,76)
insert into student values(‘張三‘,‘物理‘,90)
insert into student values(‘李四‘,‘語文‘,73)
insert into student values(‘李四‘,‘數學‘,88)
insert into student values(‘李四‘,‘物理‘,95)
--行轉列 并插入到临时表rtc
select name,
max(case course when ‘語文‘ then score else 0 end)as 語文,
max(case course when ‘數學‘ then score else 0 end)as 數學,
max(case course when ‘物理‘ then score else 0 end)as 物理
into rtc from student group by name
select * from rtc
--rtc 为原表 列转行
select * from(
select name ,‘語文‘course,語文 as score from rtc
union
select name ,‘數學‘course,數學 as score from rtc
union
select name ,‘物理‘course,物理 as score from rtc
)a order by 1 desc