Oracle11g 行列转换函数PIVOT and UNPIVOT

墨蓝 2022-10-03 00:50 186阅读 0赞

# **作为Oracle开发工程师，推荐大伙看看** #

# **PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1** #

This article shows how to use the new PIVOT and UNPIVOT operators in 11g, as well as giving a pre-11g solution to the same problems.

*  PIVOT
 *  UNPIVOT

Related articles.

*  [PIVOT and UNPIVOT Operators in Oracle Database 11g ][PIVOT and UNPIVOT Operators in Oracle Database 11g]

## **PIVOT** ##

The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. To see the PIVOT operator in action we need to create a test table.

CREATE TABLE pivot\_test (

id            NUMBER,

customer\_id   NUMBER,

product\_code  VARCHAR2(5),

quantity      NUMBER

);

INSERT INTO pivot\_test VALUES (1, 1, 'A', 10);

INSERT INTO pivot\_test VALUES (2, 1, 'B', 20);

INSERT INTO pivot\_test VALUES (3, 1, 'C', 30);

INSERT INTO pivot\_test VALUES (4, 2, 'A', 40);

INSERT INTO pivot\_test VALUES (5, 2, 'C', 50);

INSERT INTO pivot\_test VALUES (6, 3, 'A', 60);

INSERT INTO pivot\_test VALUES (7, 3, 'B', 70);

INSERT INTO pivot\_test VALUES (8, 3, 'C', 80);

INSERT INTO pivot\_test VALUES (9, 3, 'D', 90);

INSERT INTO pivot\_test VALUES (10, 4, 'A', 100);

COMMIT;

So our test data starts off looking like this.

SELECT \* FROM pivot\_test;

ID CUSTOMER\_ID PRODU   QUANTITY

\---------- ----------- ----- ----------

1           1 A             10

2           1 B             20

3           1 C             30

4           2 A             40

5           2 C             50

6           3 A             60

7           3 B             70

8           3 C             80

9           3 D             90

10           4 A            100

10 rows selected.

SQL>

In its basic form the PIVOT operator is quite limited. We are forced to list the required values to PIVOT using the IN clause.

SELECT \*

FROM   (SELECT product\_code, quantity

FROM   pivot\_test)

PIVOT  (SUM(quantity) AS sum\_quantity FOR (product\_code) IN ('A' AS a, 'B' AS b, 'C' AS c));

A\_SUM\_QUANTITY B\_SUM\_QUANTITY C\_SUM\_QUANTITY

\-------------- -------------- --------------

210             90            160

1 row selected.

SQL>

If we want to break it down by customer, we simply include the CUSTOMER\_ID column in the initial select list.

SELECT \*

FROM   (SELECT customer\_id, product\_code, quantity

FROM   pivot\_test)

PIVOT  (SUM(quantity) AS sum\_quantity FOR (product\_code) IN ('A' AS a, 'B' AS b, 'C' AS c))

ORDER BY customer\_id;

CUSTOMER\_ID A\_SUM\_QUANTITY B\_SUM\_QUANTITY C\_SUM\_QUANTITY

\----------- -------------- -------------- --------------

1             10             20             30

2             40                            50

3             60             70             80

4            100

4 rows selected.

SQL>

Prior to 11g we could accomplish a similar result using the DECODE function combined with aggregate functions.

SELECT SUM(DECODE(product\_code, 'A', quantity, 0)) AS a\_sum\_quantity,

SUM(DECODE(product\_code, 'B', quantity, 0)) AS b\_sum\_quantity,

SUM(DECODE(product\_code, 'C', quantity, 0)) AS c\_sum\_quantity

FROM   pivot\_test

ORDER BY customer\_id;

A\_SUM\_QUANTITY B\_SUM\_QUANTITY C\_SUM\_QUANTITY

\-------------- -------------- --------------

210             90            160

1 row selected.

SQL>

SELECT customer\_id,

SUM(DECODE(product\_code, 'A', quantity, 0)) AS a\_sum\_quantity,

SUM(DECODE(product\_code, 'B', quantity, 0)) AS b\_sum\_quantity,

SUM(DECODE(product\_code, 'C', quantity, 0)) AS c\_sum\_quantity

FROM   pivot\_test

GROUP BY customer\_id

ORDER BY customer\_id;

CUSTOMER\_ID A\_SUM\_QUANTITY B\_SUM\_QUANTITY C\_SUM\_QUANTITY

\----------- -------------- -------------- --------------

1             10             20             30

2             40              0             50

3             60             70             80

4            100              0              0

4 rows selected.

SQL>

Adding the XML keyword to the PIVOT operator allows us to convert the generated pivot results to XML format. It also makes the PIVOT a little more flexible, allowing us to replace the hard coded IN clause with a subquery, or the ANY wildcard.

SET LONG 10000

SELECT \*

FROM   (SELECT product\_code, quantity

FROM   pivot\_test)

PIVOT XML (SUM(quantity) AS sum\_quantity FOR (product\_code) IN (SELECT DISTINCT product\_code

FROM   pivot\_test

WHERE  id < 10));

product\_code\_XML

\----------------------------------------------------------------------------------------------------

<PivotSet><item><column name = "PRODUCT\_CODE">A</column><column name = "SUM\_QUANTITY">210</column></

item><item><column name = "PRODUCT\_CODE">B</column><column name = "SUM\_QUANTITY">90</column></item><

item><column name = "PRODUCT\_CODE">C</column><column name = "SUM\_QUANTITY">160</column></item><item>

1 row selected.

SQL>

SELECT \*

FROM   (SELECT product\_code, quantity

FROM   pivot\_test)

PIVOT XML (SUM(quantity) AS sum\_quantity FOR (product\_code) IN (ANY));

product\_code\_XML

\----------------------------------------------------------------------------------------------------

<PivotSet><item><column name = "PRODUCT\_CODE">A</column><column name = "SUM\_QUANTITY">210</column></

item><item><column name = "PRODUCT\_CODE">B</column><column name = "SUM\_QUANTITY">90</column></item><

item><column name = "PRODUCT\_CODE">C</column><column name = "SUM\_QUANTITY">160</column></item><item>

1 row selected.

SQL>

Once again, the results can be broken down by customer, with each customers XML presented as a separate row.

SET LONG 10000

SELECT \*

FROM   (SELECT customer\_id, product\_code, quantity

FROM   pivot\_test)

PIVOT XML (SUM(quantity) AS sum\_quantity FOR (product\_code) IN (SELECT DISTINCT product\_code

FROM   pivot\_test));

CUSTOMER\_ID

\-----------

PRODUCT\_CODE\_XML

\----------------------------------------------------------------------------------------------------

<PivotSet><item><column name = "PRODUCT\_CODE">A</column><column name = "SUM\_QUANTITY">10</column></i

tem><item><column name = "PRODUCT\_CODE">B</column><column name = "SUM\_QUANTITY">20</column></item><i

tem><column name = "PRODUCT\_CODE">C</column><column name = "SUM\_QUANTITY">30</column></item><item><c

olumn name = "PRODUCT\_CODE">D</column><column name = "SUM\_QUANTITY"></column></item></PivotSet>

<PivotSet><item><column name = "PRODUCT\_CODE">A</column><column name = "SUM\_QUANTITY">40</column></i

tem><item><column name = "PRODUCT\_CODE">B</column><column name = "SUM\_QUANTITY"></column></item><ite

CUSTOMER\_ID

\-----------

PRODUCT\_CODE\_XML

\----------------------------------------------------------------------------------------------------

m><column name = "PRODUCT\_CODE">C</column><column name = "SUM\_QUANTITY">50</column></item><item><col

umn name = "PRODUCT\_CODE">D</column><column name = "SUM\_QUANTITY"></column></item></PivotSet>

<PivotSet><item><column name = "PRODUCT\_CODE">A</column><column name = "SUM\_QUANTITY">60</column></i

tem><item><column name = "PRODUCT\_CODE">B</column><column name = "SUM\_QUANTITY">70</column></item><i

tem><column name = "PRODUCT\_CODE">C</column><column name = "SUM\_QUANTITY">80</column></item><item><c

olumn name = "PRODUCT\_CODE">D</column><column name = "SUM\_QUANTITY">90</column></item></PivotSet>

CUSTOMER\_ID

\-----------

PRODUCT\_CODE\_XML

\----------------------------------------------------------------------------------------------------

<PivotSet><item><column name = "PRODUCT\_CODE">A</column><column name = "SUM\_QUANTITY">100</column></

item><item><column name = "PRODUCT\_CODE">B</column><column name = "SUM\_QUANTITY"></column></item><it

em><column name = "PRODUCT\_CODE">C</column><column name = "SUM\_QUANTITY"></column></item><item><colu

mn name = "PRODUCT\_CODE">D</column><column name = "SUM\_QUANTITY"></column></item></PivotSet>

4 rows selected.

SQL>

## **UNPIVOT** ##

The UNPIVOT operator converts column-based data into separate rows. To see the UNPIVOT operator in action we need to create a test table.

CREATE TABLE unpivot\_test (

id              NUMBER,

customer\_id     NUMBER,

product\_code\_a  NUMBER,

product\_code\_b  NUMBER,

product\_code\_c  NUMBER,

product\_code\_d  NUMBER

);

INSERT INTO unpivot\_test VALUES (1, 101, 10, 20, 30, NULL);

INSERT INTO unpivot\_test VALUES (2, 102, 40, NULL, 50, NULL);

INSERT INTO unpivot\_test VALUES (3, 103, 60, 70, 80, 90);

INSERT INTO unpivot\_test VALUES (4, 104, 100, NULL, NULL, NULL);

COMMIT;

So our test data starts off looking like this.

SELECT \* FROM unpivot\_test;

ID CUSTOMER\_ID PRODUCT\_CODE\_A PRODUCT\_CODE\_B PRODUCT\_CODE\_C PRODUCT\_CODE\_D

\---------- ----------- -------------- -------------- -------------- --------------

1         101             10             20             30

2         102             40                            50

3         103             60             70             80             90

4         104            100

4 rows selected.

SQL>

The UNPIVOT operator converts this column-based data into individual rows.

SELECT \*

FROM   unpivot\_test

UNPIVOT (quantity FOR product\_code IN (product\_code\_a AS 'A', product\_code\_b AS 'B', product\_code\_c AS 'C', product\_code\_d AS 'D'));

ID CUSTOMER\_ID P   QUANTITY

\---------- ----------- - ----------

1         101 A         10

1         101 B         20

1         101 C         30

2         102 A         40

2         102 C         50

3         103 A         60

3         103 B         70

3         103 C         80

3         103 D         90

4         104 A        100

10 rows selected.

SQL>

There are several things to note about the query:

*  The required column names, in this case QUANTITY and PRODUCT\_CODE, are define in the UNPIVOT clause. These can be set to any name not currently in the driving table.
 *  The columns to be unpivoted must be named in the IN clause.
 *  The PRODUCT\_CODE value will match the column name it is derived from, unless you alias it to another value.
 *  By default the EXCLUDE NULLS clause is used. To override the default behaviour use the INCLUDE NULLS clause.

The following query shows the inclusion of the INCLUDE NULLS clause.

SELECT \*

FROM   unpivot\_test

UNPIVOT INCLUDE NULLS (quantity FOR product\_code IN (product\_code\_a AS 'A', product\_code\_b AS 'B', product\_code\_c AS 'C', product\_code\_d AS 'D'));

ID CUSTOMER\_ID P   QUANTITY

\---------- ----------- - ----------

1         101 A         10

1         101 B         20

1         101 C         30

1         101 D

2         102 A         40

2         102 B

2         102 C         50

2         102 D

3         103 A         60

3         103 B         70

3         103 C         80

ID CUSTOMER\_ID P   QUANTITY

\---------- ----------- - ----------

3         103 D         90

4         104 A        100

4         104 B

4         104 C

4         104 D

16 rows selected.

SQL>

Prior to 11g, we can get the same result using the DECODE function and a pivot table with the correct number of rows. In the following example we use the CONNECT BY clause in a query from dual to generate the correct number of rows for the unpivot operation.

SELECT id,

customer\_id,

DECODE(unpivot\_row, 1, 'A',

2, 'B',

3, 'C',

4, 'D',

'N/A') AS product\_code,

DECODE(unpivot\_row, 1, product\_code\_a,

2, product\_code\_b,

3, product\_code\_c,

4, product\_code\_d,

'N/A') AS quantity