行列互转之case when

--查看数据结构
select * from emp;
select ename,sal,deptno from emp;


--行转列，case when和max()函数
select ename,
max(case deptno when 10 then sal end) d10,
max(case deptno when 20 then sal end) d20,
max(case deptno when 30 then sal end) d30 from emp
group by ename;


--行转列，case when
select ename,
case when deptno=10 then sal end d10,
case when deptno=20 then sal end d20,
case when deptno=30 then sal end d30 from emp;


--行转列，case when的另一种形式
select ename,
case deptno when 10 then sal end d10,
case deptno when 20 then sal end d20,
case deptno when 30 then sal end d30 from emp;


--列转行，使用union,union all集合操作。
--为提高性能，建议使用临时表，少用SQL嵌套
select ename,10 deptno,d10 sal from
(select ename,
case deptno when 10 then sal end d10,
case deptno when 20 then sal end d20,
case deptno when 30 then sal end d30 from emp) where d10 is not null
union all
select ename,20 deptno,d20 sal from
(select ename,
case deptno when 10 then sal end d10,
case deptno when 20 then sal end d20,
case deptno when 30 then sal end d30 from emp) where d20 is not null
union all
select ename,30 deptno,d30 sal from
(select ename,
case deptno when 10 then sal end d10,
case deptno when 20 then sal end d20,
case deptno when 30 then sal end d30 from emp) where d30 is not null;


--把数据放入临时表
  create table temp_column as select ename,
    case deptno when 10 then sal end d10,
    case deptno when 20 then sal end d20,
    case deptno when 30 then sal end d30 from emp;
    
 create table temp_line as select * from
  (select ename,10 deptno,d10 sal from temp_column where d10 is not null
  union all
  select ename,20 deptno,d20 sal from temp_column where d20 is not null
  union all
  select ename,30 deptno,d30 sal from temp_column where d30 is not null);
  --drop table temp_column;
  --drop talbe temp_line;


--查询处理后的数据
select * from temp_column;
select * from temp_line;


create table fruit
(imonth varchar2(10),
ename varchar2(10),
sale number);
--drop table fruit;


insert into fruit values('1月','杨梅',11);
insert into fruit values('1月','蓝莓',22);
insert into fruit values('1月','樱桃',33);
insert into fruit values('2月','杨梅',111);
insert into fruit values('2月','蓝莓',222);
insert into fruit values('2月','樱桃',333);


select * from fruit;


select imonth,
 case ename when '杨梅' then sale end 杨梅,
 case ename when '蓝莓' then sale end 蓝莓,
 case ename when '樱桃' then sale end 樱桃 from fruit;


create table temp_friut_column as
select imonth,
 max(case ename when '杨梅' then sale end) 杨梅,
 max(case ename when '蓝莓' then sale end) 蓝莓,
 max(case ename when '樱桃' then sale end) 樱桃 from fruit
 group by imonth;
 
select imonth,'杨梅' ename,杨梅 sale from temp_friut_column
union all
select imonth,'蓝莓' ename,蓝莓 sale from temp_friut_column
union all
select imonth,'樱桃' ename,樱桃 sale from temp_friut_column;