Oracle 几种行转列的方式 sum+decode sum+case when pivot

 目录

原始数据：

方式一：

方式二：

方式三：

unpivot的使用：

原始数据：

方式一：

select t_name,
       sum(decode(t_item, 'item1', t_num, 0)) item1,
       sum(decode(t_item, 'item2', t_num, 0)) item2,
       sum(decode(t_item, 'item3', t_num, 0)) item3,
       sum(t_num) total
  from test
 group by t_name;

方式二：

select t_name,
       sum(case
             when t_item = 'item1' then
              t_num
             else
              0
           end) item1,
       sum(case
             when t_item = 'item2' then
              t_num
             else
              0
           end) item2,
       sum(case
             when t_item = 'item3' then
              t_num
             else
              0
           end) item3,
       sum(t_num) total
  from test
 group by t_name;

方式三：

 select t.*, (nvl(t.item1, 0) + nvl(t.item2, 0) + nvl(t.item3, 0)) as total
   from (select *
           from test pivot(sum(t_num) for t_item in('item1' as item1,
                                                    'item2' as item2,
                                                    'item3' as item3))) t;

unpivot的使用：

 select t_name, t_item, t_num
 from (

    select *
      from test pivot(sum(t_num) for t_item in('item1' as item1,
                                               'item2' as item2,
                                               'item3' as item3))

 ) unpivot(t_num for t_item in(item1,item2,item3));