SQL延伸2-最简单的行列互换

--  0.1准备工作，建表 ，和初始化数据
    create table t_table1 (
    col1 varchar2(3),
    col2 number ) ;

    insert into t_table1 values ('a',11);
    insert into t_table1 values ('b',21);
    insert into t_table1 values ('c',31);
    insert into t_table1 values ('d',41);
    commit;

--  0.2.验证准备工作
    select * from t_table1 ;

--  1.用UNION ALL
    select sum(a) a,sum(b) b,sum(c) c ,sum(d) d 
    from (
          select col2 a, 0    b, 0    c, 0    d from t_table1 where col1='a'
          union all
          select 0    a, col2 b, 0    c, 0    d from t_table1 where col1='b'
          union all
          select 0    a, 0    b, col2 c, 0    d from t_table1 where col1='c'
          union all
          select 0    a, 0    b, 0    c, col2 d from t_table1 where col1='d'
          ) ;
 
--  2. 用case when then
    select 
        max(case when col1 = 'a' then col2 else 0  end) a, 
        max(case when col1 = 'b' then col2 else 0  end) b,
        max(case when col1 = 'c' then col2 else 0  end) c,
        max(case when col1 = 'd' then col2 else 0  end) d
    from t_table1;
    -- 除了max 也可以用sum。因为别的值都是0

--  3.用decode(oracle数据库专用)
    select 
        max(decode(col1,'a',col2,0)) a, 
        max(decode(col1,'b',col2,0)) b,
        max(decode(col1,'c',col2,0)) c,
        max(decode(col1,'d',col2,0)) d
    from t_table1; 
    -- 以下两个是理解decode的用法
    select 
        decode(col1,'a',col2) a, 
        decode(col1,'b',col2) b,
        decode(col1,'c',col2) c,
        decode(col1,'d',col2) d
    from t_table1;

    select 
        decode(col1,'a',col2,0) a, 
        decode(col1,'b',col2,0) b,
        decode(col1,'c',col2,0) c,
        decode(col1,'d',col2,0) d
    from t_table1;

--  4 用pivot 处理 (oracle数据库专用)
    select * 
     from  t_table1
     pivot ( max(col2)
       for col1 in ('a','b','c','d')
       );