Oracle语法：pivot

发表于 | 分类于 ， |
| 字数统计: 1,250

PIVOT

pivot是一个在Oracle中输出EXCEL报表时非常有用的函数，它可以将字段值转成列名，并且同时做一些聚合操作。

语法格式：pivot(聚合函数 for 需要转为列的字段名 in(需要转为列的字段值，可多个))

1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM (
    -- 源数据查询
    SELECT column1, column2, ..., pivot_column, value_column
    FROM your_source_table
)
PIVOT (
    -- 聚合函数和列定义
    aggregate_function(value_column)
    FOR pivot_column IN (value1 AS alias1, value2 AS alias2, ..., valuen AS aliasn)
);

  • aggregate_function：指定用于对value_column进行聚合操作的函数，如SUM、AVG等。(FOR关键字前面的部分只能使用聚合函数)
  • value_column: 指定要聚合的源数据列。
  • pivot_column: 指定要透视的列，其唯一值将被用作新列的列头。且源数据查询的select中必须包含这个字段，以便PIVOT函数可以使用到它。（可以理解为用这个字段来进行group by）
  • value1 AS alias1, value2 AS alias2, …, valuen AS aliasn: 为透视列的每个唯一值指定一个别名，这些别名将成为新列的列头。遗憾的是这里不是使用子查询。

示例1

有一个产品销售表如下：

1
2
3
4
5
6
7
8
CREATE TABLE PRODUCT_SELL
(
   ID                   NUMBER(10)           not null,
   PRODUCT_NAME         VARCHAR2(50),
   SELL_TIME            DATE,
   SELL_COUNT           NUMBER(10),
   CONSTRAINT PK_PRODUCT primary key (ID)
);

插入测试数据：

如果我们要根据2018年各产品销售量给出各个月销售数量表，sql可以写成这样：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT *            --这里只能用 “*” 号，或者没有进行转换的字段，比如这里的PRODUCT_NAME，而不能含有SELL_COUNT、MON这两个字段
FROM 
(
    SELECT A.PRODUCT_NAME, TO_CHAR(A.SELL_TIME,'mm') AS MON, A.SELL_COUNT 
    FROM PRODUCT A 
    WHERE TO_CHAR(A.SELL_TIME,'yyyy') = '2018'
)
PIVOT
(
    SUM(SELL_COUNT) FOR MON IN
    (
        '01' M1,        --这里也可以写成   '01' AS M1
        '02' M2,
        '03' M3,
        '04' M4,
        '05' M5,
        '06' M6,
        '07' M7,
        '08' M8,
        '09' M9,
        '10' M10,
        '11' M11,
        '12' M12
    )
);

sql执行的结果如下：

示例2

数据准备如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE TABLE sales_data (
    product_name VARCHAR2(100),
    region VARCHAR2(50),
    sale_month VARCHAR2(10),
    sale_amount NUMBER
);
-- 商品 A 在不同地区的销售数据
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'North', '2024-01', 5000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'South', '2024-01', 7000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'West', '2024-01', 4500);

INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'North', '2024-02', 8000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'South', '2024-02', 7500);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'West', '2024-02', 6000);

INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'North', '2024-03', 7000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'South', '2024-03', 8500);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product A', 'West', '2024-03', 6200);

-- 商品 B 在不同地区的销售数据
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'North', '2024-01', 6000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'South', '2024-01', 8000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'West', '2024-01', 5500);

INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'North', '2024-02', 7000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'South', '2024-02', 9000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'West', '2024-02', 6500);

INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'North', '2024-03', 7800);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'South', '2024-03', 9200);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product B', 'West', '2024-03', 6900);

-- 商品 C 在不同地区的销售数据
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'North', '2024-01', 5500);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'South', '2024-01', 6000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'West', '2024-01', 4800);

INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'North', '2024-02', 6500);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'South', '2024-02', 7000);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'West', '2024-02', 5800);

INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'North', '2024-03', 7200);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'South', '2024-03', 7800);
INSERT INTO sales_data (product_name, region, sale_month, sale_amount) VALUES ('Product C', 'West', '2024-03', 6000);

比较如下两段SQL及查询结果：

1
2
3
4
5
-- 以地区为行 商品为列
SELECT
	* 
FROM
	( SELECT product_name, region, SALE_AMOUNT  FROM sales_data ) PIVOT ( sum( SALE_AMOUNT ) FOR product_name IN ( 'Product A', 'Product B', 'Product C' ) ) ORDER BY region;

查询结果为：

1
2
3
4
SELECT
	* 
FROM
	sales_data PIVOT ( sum( SALE_AMOUNT ) AS sum, avg( SALE_AMOUNT ) AS avg FOR product_name IN ( 'Product A', 'Product B', 'Product C' ) ) ORDER BY region;

查询结果为：

可以看出，这段SQL是查询每个地区、每个月的商品销售额。和前一段SQL的不同之处，是查询的数据源是sales_data表，而不是这个 ( SELECT product_name, region, SALE_AMOUNT FROM sales_data ) 进行了字段选择的临时表，可以看出PIVOT实际上会用除 pivot_column 和 value_column 以外的字段组合当成唯一键（类似group by）进行分组统计。

示例3

数据准备如下：

如下sql语句（in中使用子查询）：

1
2
3
4
5
6
select * from T_Student_Grades
pivot
(
    count(grades)
    for name in (select distinct name from T_Student_Grades)
)

报错提示：ORA-00936：缺失表达式。所以可以看出in不支持子查询。

UNPIVOT

UNPIVOT函数的功能和PIVOT相反，可以将多个列转为一行。

------ 本文完 ------