Oracle中行转列（pivot）函数解析（二）

Oracle行转列就是把某一个字段的值作为唯一值，然后另外一个字段的行值转换成它的列值。

案例原始数据如下：

 方法一：利用group by实现

select t.column_a,
       sum(decode(dm, '01', t.je, 0)) 费1,
       sum(decode(dm, '02', t.je, 0)) 费2,
       sum(decode(dm, '03', t.je, 0)) 费3,
       sum(decode(dm, '04', t.je, 0)) 费4,
       sum(decode(dm, '05', t.je, 0)) 费5,
       sum(decode(dm, '06', t.je, 0)) 费6,
       sum(decode(dm, '07', t.je, 0)) 费7,
       sum(decode(dm, '08', t.je, 0)) 费8,
       sum(decode(dm, '09', t.je, 0)) 费9,
       sum(decode(dm, '10', t.je, 0)) 费10,
       sum(decode(dm, '11', t.je, 0)) 费11,
       sum(decode(dm, '12', t.je, 0)) 费12,
       sum(decode(dm, '13', t.je, 0)) 费13,
       sum(decode(dm, '14', t.je, 0)) 费14
  from usera.table_a t
 where t.column_a = '110000557222'
 group by t. column_a

 方法二：Oracle11g之后提供了自带函数PIVOT可以完美解决这个行转列的需求，具体语法结构如下：

select *
  from (select column_a, dm, sum(je) fee
          from usera.table_a
         where column_a = '110000557222'
         group by column_a, dm)
pivot(sum(fee)
   for dm in('01' as new_column_a,
               '02' as new_column_b,
               '03' as new_column_c,
               '04' as new_column_d,
               '05' as new_column_e,
               '06' as new_column_f,
               '07' as new_column_g,
               '08' as new_column_h,
               '09' as new_column_i,
               '10' as new_column_j,
               '11' as new_column_k,
               '12' as new_column_l,
               '13' as new_column_m,
               '14' as new_column_n))

 参考博客：https://blog.csdn.net/langweixiana/article/details/116011508