oracle非常量不能用于privot_Oracle11g 行列转换函数PIVOT and UNPIVOT

作为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.

Related articles.

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.

<