sql-行转列(转置)

- 行转列的常规做法是，group by+sum(if())【或count(if())】

例题：
已知

查成这样一个结果

解答：

use test_sql;
set hive.exec.mode.local.auto=true;
create table table2(year int,month int ,amount double) ;
insert overwrite table table2 values
           (1991,1,1.1),
           (1991,2,1.2),
           (1991,3,1.3),
           (1991,4,1.4),
           (1992,1,2.1),
           (1992,2,2.2),
           (1992,3,2.3),
           (1992,4,2.4);
select * from table2;


--行转列
--常规做法是，group by+sum(if())
--SQLserver中有pivot专门用来行转列
--原始写法
select 
	year
    ,sum(a) as m1
    ,sum(b) as m2
    ,sum(c) as m3
    ,sum(d) as m4
from(
	select 
		*
        ,if(month=1,amount,0) a
        ,if(month=2,amount,0) b
        ,if(month=3,amount,0) c
        ,if(month=4,amount,0) d
    from table2 ) t
group by t.year;
--简化写法
select 
	year,
	,sum(if(month=1,amount,0)) m1
	,sum(if(month=2,amount,0)) m2
    ,sum(if(month=3,amount,0)) m3
    ,sum(if(month=4,amount,0)) m4
from table2
group by year;