pivot & unpivot（行列转换）

一、pivot

The data returned by business intelligence queries is often most usable if presented in a crosstabular format.

The pivot_clause of the SELECT statement lets you write crosstabulation queries that rotate rows into columns, aggregating data in the process of the rotation.

select语句中使用转置子句，可以完成交叉表的查询：行转列，聚合数据

Pivoting is a key technique in data warehouses. In it, you transform multiple rows of input into fewer and generally wider rows in the data warehouse.

转置是数据仓库中的关键技术。在数据仓库中，使用转置，可以将多行数据转为较少的数据行输出

When pivoting, an aggregation operator is applied for each item in the pivot column value list.

使用转置技术时，每个item都要有聚合操作

The pivot column cannot contain an arbitrary expression. If you need to pivot on an expression, then you should alias the expression in a view before the PIVOT operation.

转置列不能包含表达式。如果你想要转置表达式，应该在转置操作前，给表达式别名。

二、基本语法

The basic syntax is as follows:

基本语法：

SELECT ....

FROM <table-expr>

   PIVOT

     (

      aggregate-function(<column>)

      FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)

        ) AS <alias>

WHERE .....

三、示例：

1、单列转置（行转列）

To illustrate the use of pivoting, create the following view as a basis for later examples:

CREATE VIEW sales_view AS

SELECT

 prod_name product, country_name country, channel_id channel,

 SUBSTR(calendar_quarter_desc, 6,2) quarter,

 SUM(amount_sold) amount_sold, SUM(quantity_sold) quantity_sold

FROM sales, times, customers, countries, products

WHERE sales.time_id = times.time_id AND

  sales.prod_id = products.prod_id AND

  sales.cust_id = customers.cust_id AND

  customers.country_id = countries.country_id

GROUP BY prod_name, country_name, channel_id,

 SUBSTR(calendar_quarter_desc, 6, 2);

 

--

The following statement illustrates a typical  pivot on the channel column:

1）分组字段为product

SELECT * FROM

  (SELECT product, channel, amount_sold

   FROM sales_view

   ) S PIVOT (SUM(amount_sold)

   FOR CHANNEL IN (3 AS DIRECT_SALES, 4 AS INTERNET_SALES,

                   5 AS CATALOG_SALES, 9 AS TELESALES))

ORDER BY product;

2）分组字段为country

3）分组字段，product,country

...

Note that the output has created four new aliased columns, DIRECT_SALES, INTERNET_SALES, CATALOG_SALES, and TELESALES, one for each of the pivot values. The output is a sum.

If no alias is provided, the column heading will be the values of the IN-list.

输出结果中，新产生了4个别名列，DIRECT_SALES, INTERNET_SALES, CATALOG_SALES, and TELESALES，对应不同的转置列的值。如果没有别名的话，列标题就是 in list中的pivot column value

2、多字段转置Pivoting on Multiple Columns

多列转置

You can pivot on more than one column. The following statement illustrates a typical multiple column pivot:

--用于和下面的sql比较输出结果：

select PRODUCT,CHANNEL,QUARTER,SUM(quantity_sold) from SALES_VIEW

group by PRODUCT,CHANNEL,quarter

order by PRODUCT；

SELECT *

FROM

     (SELECT product, channel, quarter, quantity_sold

      FROM sales_view

     ) PIVOT (SUM(quantity_sold)

                FOR (channel, quarter) IN

                  ((5, '02') AS CATALOG_Q2,

                   (4, '01') AS INTERNET_Q1,

                   (4, '04') AS INTERNET_Q4,

                   (2, '02') AS PARTNERS_Q2,

                   (9, '03') AS TELE_Q3

                  )

                );

上面的sql：product是隐含的分组字段, channel,和quarter是pivoting columns，quantity_sold是聚合字段（sum）。

输出结果为6列：

product列

SUM(quantity_sold) as CATALOG_Q2,

INTERNET_Q1

INTERNET_Q4

PARTNERS_Q2

TELE_Q3 

3、Pivoting: Multiple Aggregates，多个聚合函数

You can pivot with multiple aggregates, as shown in the following example:

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 (5, 4, 2, 9)

               )

ORDER BY product;

Note that the query creates column headings by concatenating the pivot values (or alias) with the alias of the aggregate function, plus an underscore

输出结果的标题：pivot values连接上aggregate function

4、Distinguishing PIVOT-Generated Nulls from Nulls in Source Data

create table SALE2

(

PROD_ID int,

QTR varchar2(5),

AMOUNT_SOLD int

)

insert into SALE2 values(100,'Q1',10);

insert into SALE2 values(100,'Q1',20);

insert into SALE2 values(100,'Q2',null);

insert into SALE2 values(200,'Q1',50);

You can distinguish between null values that are generated from the use of PIVOT and those that exist in the source data. The following example illustrates nulls that PIVOT generates.

The following query returns rows with 5 columns, column prod_id, and pivot resulting columns Q1, Q1_COUNT_TOTAL, Q2, Q2_COUNT_TOTAL. For each unique value of prod_id,Q1_COUNT_TOTAL returns the total number of rows whose qtr value is Q1, that is, and Q2_COUNT_TOTAL returns the total number of rows whose qtr value is Q2.

Assume we have a table sales2 of the following structure:

PROD_ID   QTR  AMOUNT_SOLD
-------   ---  -----------
100        Q1           10
100        Q1           20
100        Q2         NULL
200        Q1           50

SELECT *
FROM sales2
        PIVOT
         ( SUM(amount_sold), COUNT(*) AS count_total
           FOR qtr IN ('Q1', 'Q2')
         );

PROD_ID    "Q1"   "Q1_COUNT_TOTAL"  "Q2"       "Q2_COUNT_TOTAL"
-------    ----   ----------------  ---------  ----------------
    100      20                  2   NULL <1>                 1
    200      50                  1   NULL <2>                 0

From the result, we know that for prod_id 100, there are 2 sales rows for quarter Q1, and 1 sales row for quarter Q2; for prod_id 200, there is 1 sales row for quarter Q1, and no sales row for quarter Q2.So, in Q2_COUNT_TOTAL, you can identify that NULL<1> comes from a row in the original table whose measure is of null value, while NULL<2> is due to no row being present in the original table for prod_id 200 in quarter Q2.

四、Unpivoting Operations

An unpivot does not reverse a PIVOT operation. Instead, it rotates data from columns into rows. If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means. 实现列转行

1、创建pivoted data

To illustrate unpivoting, first create a pivoted table that includes four columns, for quarters of the year:

CREATE TABLE pivotedTable AS

SELECT *

FROM (SELECT product, quarter, quantity_sold, amount_sold

      FROM sales_view)

    PIVOT

    (

       SUM(quantity_sold) AS sumq, SUM(amount_sold) AS suma

       FOR quarter IN ('01' AS Q1, '02' AS Q2, '03' AS Q3, '04' AS Q4));

表pivotedTable 输出如下：（共71行）

The following  UNPIVOT  operation rotates the quarter columns into rows. For each product, there will be four rows, one for each quarter.

SELECT product, DECODE(quarter, 'Q1_SUMQ', 'Q1', 'Q2_SUMQ', 'Q2', 'Q3_SUMQ', 'Q3',  --decode 函数只是用来改变输出结果的列标题

   '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;

--上面的语句与下面的sql等价：

select PRODUCT, QUARTER1,

        --DECODE(QUARTER1, 'Q1_SUMQ', 'Q1', 'Q2_SUMQ', 'Q2', 'Q3_SUMQ', 'Q3', 'Q4_SUMQ', 'Q4') AS QUARTER1,

         quantity_sold1

from PIVOTEDTABLE

   UNPIVOT INCLUDE nulls

       (QUANTITY_SOLD1

        for QUARTER1 in ( Q1_SUMQ as 'Q1', Q2_SUMQ as 'Q2', Q3_SUMQ as 'Q3', Q4_SUMQ as 'Q4' ))  --改变输出结果的列标题

order by PRODUCT, QUARTER1;

输出结果集是 pivotedTable  的4倍。（原来 pivotedTable  表的一行数据转置为4行数据 ）

2、比较include nulls和exclude nulls

Note the use of INCLUDE NULLS in this example. You can also use EXCLUDE NULLS, which is the default setting.

3、unpivot using two columns

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;

等价sql 1：

SELECT product, quarter2, quantity_sold2, amount_sold2

FROM pivotedTable

   UNPIVOT INCLUDE NULLS

       (

        (QUANTITY_SOLD2, AMOUNT_SOLD2)

        for QUARTER2 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, quarter2;

输出结果：

五、Wildcard and Subquery Pivoting with XML Operations

if you want to use a wildcard argument or subquery in your pivoting columns, you can do so with PIVOT XML syntax. With PIVOT XML, the output of the operation is properly formatted XML.

The following example illustrates using the wildcard keyword,  ANY . It outputs XML that includes all channel values in  sales_view :

Note that the keyword ANY is available in PIVOT operations only as part of an XML operation. This output includes data for cases where the channel exists in the data set. Also note that aggregation functions must specify a GROUP BY clause to return multiple values, yet the pivot_clause does not contain an explicit GROUP BY clause. Instead, the pivot_clause performs an implicit GROUPBY.  

隐式group by，不用强制加group by

The following example illustrates using a subquery. It outputs XML that includes all channel values and the sales data corresponding to each channel:

SELECT *

FROM

     (SELECT product, channel, quantity_sold

      FROM sales_view

     ) PIVOT XML(SUM(quantity_sold)

                for CHANNEL in (select distinct CHANNEL_ID from CHANNELS)

             );

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28194062/viewspace-1755575/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28194062/viewspace-1755575/