oracle pivot，unpivot基本语法

た入场券 2021-09-28 01:32 377阅读 0赞

### 文章目录 ###

*   *   *  适用版本，oracle11及以上。
         *  pivot
         *   *  老方法
             *  新方法
         *  unpivot
         *  xml

### 适用版本，oracle11及以上。 ###

### pivot ###

#### 老方法 ####

用于行转列。在11g之前，一般用这样的方法来做

对于这样的表

select 1 id, 'a1' typ, 1 val from dual union all
    select 1, 'a2' , 2 val from dual union all
    select 1, 'a3' , 3 val from dual union all
    select 2, 'a1' , 4 val from dual union all
    select 2, 'a3' , 5 val from dual

我现在想要将每一个id所对应的typ横着显示，需要这样写

with tab1 as (
    select 1 id, 'a1' typ, 1 val from dual union all
    select 1, 'a2' , 2 val from dual union all
    select 1, 'a3' , 3 val from dual union all
    select 2, 'a1' , 4 val from dual union all
    select 2, 'a3' , 5 val from dual 
    )
    select id,
           max(decode(typ, 'a1', val)) a1,
           max(decode(typ, 'a2', val)) a2,
           max(decode(typ, 'a3', val)) a3
      from tab1
     group by id
    ;

#### 新方法 ####

适用pivot，可以这样写

with tab1 as (
    select 1 id, 'a1' typ, 1 val from dual union all
    select 1, 'a2' , 2 val from dual union all
    select 1, 'a3' , 3 val from dual union all
    select 2, 'a1' , 4 val from dual union all
    select 2, 'a3' , 5 val from dual 
    )
    select*from tab1
    pivot(max(val) for typ in('a1' as aaa, 'a2' as a2, 'a3'))
    ;

### unpivot ###

重功能上来说，可以看成是pivot的逆运算，只是行列变换所需的列会由于手动指定的关系而缺失或者改变一些值。

with tab1 as (
    select 1 id, 'a1' typ, 1 val from dual union all
    select 1, 'a2' , 2 val from dual union all
    select 1, 'a3' , 3 val from dual union all
    select 2, 'a1' , 4 val from dual union all
    select 2, 'a3' , 5 val from dual 
    )
    select*from tab1
    pivot(max(val) for typ in('a1' as aaa, 'a2' as a2, 'a3' a3))
    unpivot(val for typ in (aaa as 'a', a2 as 'b', a3 as 'c'))
    ;

### xml ###

经常看到有人问怎么把in中的部分写成动态的，我通过官方文档得知，对于以xml格式存储的数据是可以做到的。不过从来没遇到的那样的表，先不研究了。