Oracle SQL函数pivot、unpivot转置函数实现行转列、列转行

函数PIVOT、UNPIVOT转置函数实现行转列、列转行，效果如下图所示：
.PIVOT为行转列，从图示的左边到右边
.UNPIVOT为列转行，从图示的右边到左边
.左边为纵表，结构简单，易扩展
.右边为横表，展示清晰，方便查询
.很多时候业务表为纵表，但是统计分析需要的结果如右边的横表，这时候就需要用到转置函数了
示例图表：
Pivot语法：
    SELECT ....
    FROM <table-expr>
       PIVOT
         (
          aggregate-function(<column>)
          FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)
            ) AS <alias>
    WHERE .....
注意：
FOR <pivot-column>
这个是不支持表达式的，如果需要，请通过子查询或者视图先预处理。
Pivot
例子1：先构造一个子查询，然后根据CHANNEL列进行转置，源表sales_view里面可能有很多列，不需要列先通过子查询过滤掉再进行转置。
另外转置后的列指定了别名，值是对amount_sold列的汇总。
    SELECT * FROM
      (SELECT product, channel, amount_sold
       FROM sales_view
       ) S PIVOT (SUM(amount_sold)
       FOR CHANNEL IN ( AS DIRECT_SALES,  AS INTERNET_SALES, 
                        AS CATALOG_SALES,  AS TELESALES))
    ORDER BY product;
     
    PRODUCT                  DIRECT­_SALES  INTERNET_SALES  CATALOG_SALES  TELESALES
    ----------------------   ------------  --------------  -------------  ---------
    ...
    Internal 6X CD-ROM          229512.97        26249.55
    Internal 8X CD-ROM          286291.49        42809.44
    Keyboard Wrist Rest         200959.84        38695.36                   1522.73
    ... 
例子2：基于多列进行转置，下面例子是基于channel、quarter两列进行转置
    SELECT *
    FROM
         (SELECT product, channel, quarter, quantity_sold
          FROM sales_view
         ) PIVOT (SUM(quantity_sold)
                    FOR (channel, quarter) IN
                      ((, '') AS CATALOG_Q2,
                       (, '') AS INTERNET_Q1,
                       (, '') AS INTERNET_Q4,
                       (, '') AS PARTNERS_Q2,
                       (, '') AS TELE_Q3
                      )
                    );
     
    PRODUCT              CATALOG_Q2  INTERNET_Q1  INTERNET_Q4  PARTNERS_Q2   TELE_Q3
    -------              ----------  -----------  -----------  -----------   -------
    ...
    Bounce                                                       
    ...      
    Smash Up Boxing                                              
    ...  
    Comic Book Heroes                                             
    ...
例子3：对多列的值进行汇总计算，以下是基于channel例进行转置，然后对amount_sold和quantity_sold两列进行合计运算
    SELECT *
    FROM
         (SELECT product, channel, amount_sold, quantity_sold
          FROM sales_view
         ) PIVOT (SUM(amount_sold) AS sums,
                  SUM(quantity_sold) AS sumq
                  FOR channel IN (, , , )
                   )
    ORDER BY product;
     
    PRODUCT                5_SUMS  5_SUMQ    4_SUMS   4_SUMQ      2_SUMS   2_SUMQ    9_SUMS   9_SUMQ
    -------------          ------  ------    ------   ------      ------   ------    ------   ------
    O/S Doc Set English                   142780.36        381397.99        6028.66      
    O/S Doc Set French                     55503.58        132000.77        
    ... 
Unpivot
unpivot是pivot的相反操作，进行的是列转行
例子1：先看源表结构，for子句指定将（Q1_SUMQ, Q2_SUMQ, Q3_SUMQ, Q4_SUMQ）这4列转置为行，
for子句之前的quantity_sold是4列转置后的列名，
decode还定义了每列转置为行后新标示列的值，这个等下看第2个例子可以看到，也可以在 in 子句后面加 as 指定别名。
UNPIVOT INCLUDE NULLS 指定空值也进行转置，如果是EXCLUDE NULLS 将忽略空值。
    SELECT *
    FROM pivotedTable
    ORDER BY product;
     
    PRODUCT         Q1_SUMQ  Q1_SUMA  Q2_SUMQ  Q2_SUMA   Q3_SUMQ  Q3_SUMA   Q4_SUMQ    Q4_SUMA
    --------------- -------  -------  -------  --------  -------  --------  -------    ---------
    .44MB External      58301.33       49001.56       56974.3          55341.28
    128MB Memory        110763.63      132123.12      170710.4         157736.6
    " LCD            1812786.94     1672389.06     1859987.66       1844008.11
    SELECT product, DECODE(quarter, 'Q1_SUMQ', 'Q1', 'Q2_SUMQ', 'Q2', 'Q3_SUMQ', 'Q3',
       'Q4_SUMQ', 'Q4') AS quarter, quantity_sold
    FROM  pivotedTable
       UNPIVOT INCLUDE NULLS
           (quantity_sold
            FOR quarter IN (Q1_SUMQ, Q2_SUMQ, Q3_SUMQ, Q4_SUMQ))
    ORDER BY product, quarter;
     
    PRODUCT                          QUARTER      QUANTITY_SOLD
    -------                          --           -------------
    .44MB External 3.5" Diskette    Q1             
    .44MB External 3.5" Diskette    Q2             
    .44MB External 3.5" Diskette    Q3             
    .44MB External 3.5" Diskette    Q4             
    128MB Memory Card                Q1             
    128MB Memory Card                Q2             
    128MB Memory Card                Q3             
    128MB Memory Card                Q4             
    ...
例子2：转置多列的情况
    SELECT product, quarter, quantity_sold, amount_sold
    FROM  pivotedTable
       UNPIVOT INCLUDE NULLS
           (
            (quantity_sold, amount_sold)
            FOR quarter IN ((Q1_SUMQ, Q1_SUMA) AS 'Q1', (Q2_SUMQ, Q2_SUMA) AS 'Q2', (Q3_SUMQ, Q3_SUMA) AS 'Q3', (Q4_SUMQ, Q4_SUMA) AS 'Q4'))
    ORDER BY product, quarter;
     
    PRODUCT                          QU   QUANTITY_SOLD   AMOUNT_SOLD
    -----------------------------    --   -------------   ------------
    .44MB External 3.5" Diskette    Q1                   58301.33
    .44MB External 3.5" Diskette    Q2                   49001.56
    .44MB External 3.5" Diskette    Q3                   56974.3
    .44MB External 3.5" Diskette    Q4                   55341.28
    128MB Memory Card                Q1                  110763.63
    128MB Memory Card                Q2                  132123.12
    128MB Memory Card                Q3                  170710.4
    128MB Memory Card                Q4                  157736.6
总结，基本上按照语法套用即可，注意将源表非相关列先过滤掉，可是是子查询，也可以是视图。 
--------------------- 
作者：Seandba 
来源：CSDN 
原文：https://blog.csdn.net/seandba/article/details/?utm_source=copy 
版权声明：本文为博主原创文章，转载请附上博文链接！