Oracle语法：pivot和unpivot

发表于 更新于 分类于 ， 本文字数： 1.5k 阅读时长 ≈ 5 分钟

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
6
7
8
9
10
11
-- 以地区为行 商品为列
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
5
6
7
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相反，可以将多个列转为行。

语法

1
2
3
4
5
6
7
8
9
SELECT *
FROM (source_table)
UNPIVOT 
(
  new_column_name FOR column_to_unpivot IN 
  (
  	list_of_columns
  )
)

  • new_column_name：要转成行的列的值，转换后归属的新的列名。

  • column_to_unpivot：要转成行的列的名称，转换后归属的新的列名。

  • list_of_columns：要转成成行的列，组成的一个列表。

示例1

以下是一个示例，演示如何使用UNPIVOT函数实现列转行效果。

  1. 创建成绩表并插入数据：
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE tscores (
    id NUMBER,
    name VARCHAR2(20),
    chinese_score NUMBER,
    math_score NUMBER,
    english_score NUMBER
);

INSERT INTO tscores (id, name, chinese_score, math_score, english_score) VALUES (1, 'John', 90, 80, 85);
INSERT INTO tscores (id, name, chinese_score, math_score, english_score) VALUES (2, 'Alice', 95, 75, 90);
INSERT INTO tscores (id, name, chinese_score, math_score, english_score) VALUES (3, 'Bob', 85, 90, 80);
INSERT INTO tscores (id, name, chinese_score, math_score, english_score) VALUES (4, 'Mary', 92, 88, 95);
COMMIT;
  1. 使用UNPIVOT函数进行列转行操作：
1
2
3
4
5
6
7
8
9
SELECT id, name, subject, score
FROM tscores
UNPIVOT (
    score FOR subject IN (
        chinese_score AS 'Chinese',
        math_score AS 'Math',
        english_score AS 'English'
    )
) order by name,subject;

运行以上代码，将会得到以下结果：

ID NAME SUBJECT SCORE
2 Alice Chinese 95
2 Alice English 90
2 Alice Math 75
3 Bob Chinese 85
3 Bob English 80
3 Bob Math 90
1 John Chinese 90
1 John English 85
1 John Math 80
4 Mary Chinese 92
4 Mary English 95
4 Mary Math 88

以上示例中，原始表scores包含了列chinese_score、math_score和english_score。通过UNPIVOT函数，将这些列转换为行，每行包含了学生的id、name、科目和成绩。最终查询结果显示了每位学生的不同科目成绩。

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