Kingbase-查询-PIVOT 和 UNPIVOT 行列转换

9 PIVOT 和 UNPIVOT 行列转换

PIVOT 和 UNPIVOT 是 SELECT 的一种扩展查询方式。 PIVOT 查询涉及将行置转换为列，或者使用 UNPIVOT

将列置转换为行，以交叉表格式生成结果。

PIVOT 操作为行转列，并在转换过程中聚合数据，新的列表示不同范围的聚合数据， PIVOT 操作的输出通

常比初始数据行包含更多的列和更少的行。 UNPIVOT 是 PIVOT 的反操作，它是将多个列的内容合并到同一

个列中，以多行形式展示。

关于输入结果有如下使用限制：

输入为一个查询或者子查询的结果集，当为一个子查询的时候，支持目标列的类型可以为列名、

常量或者表达式，但是为常量或者表达式的时候必须使用别名，表达式中支持使用列名和常量进

行计算，且子查询只支持单表为输入且不支持表使用别名。

9.1 PIVOT 行转列

PIVOT 操作是将查询结果集进行行转列

SELECT select_list

FROM table_expression

[ PIVOT aggfunction FOR column ... IN (( 'column_const' , 'column_const' ... ) [AS alais], .

, → ... ) ]

PIVOT 首先计算指定的聚合函数的值。通常聚合函数必须指定 GROUP BY 子句才能返回多个值，但是在

PIVOT 子句不包含显式的 GROUP BY 子句。在 PIVOT 子句执行隐式 GROUP BY 。隐式 GROUP BY 是基于

所有没有在 PIVOT 子句中引用的列，以及在在 PIVOT 的 IN 子句中指定的一组值。

根据指定的分组列和聚合值，生成的交叉表包含以下列：

a. 所有没在 PIVOT 子句中引用的隐式分组列值。

b. 以及与 PIVOT 的 IN 子句中的值对应的新列，每个聚合的值被转换到适当的新列中。数据库将为每个

新列生成一个名称：如果没有为聚合函数提供别名，则使用每个 pivot 列的值作为聚合函数转置到的每

个新列的名称。如果为聚合函数提供别名，则连接 PIVOT 列名、下划线字符和聚合函数别名为每个新

列生成的一个名称。

一个转换单列的例子 :

select * from pivot_t1 ;

month |

name

| nums | other

-------+--------+------+-------

1 | apple

| 1000 | a

2 | apple

| 2000 | d

3 | apple

| 4000 | a

1 | orange |

400 | d

2 | orange |

500 | b

3 | orange |

500 | b

1 | grape

| 3500 | c

2 | grape

| 3500 | c

3 | grape

| 3500 | c

( 9 rows)

select * from

(select month,name,nums from pivot_t1 ) pivot ( sum (nums) for name in (

, → 'apple' as pingguo , 'orange' as juzi , 'grape' as putao));

month | pingguo | juzi | putao

-------+---------+------+-------

(continues on next page)

(continued from previous page)

1 |

1000 |

400 |

3500

2 |

2000 |

500 |

3500

3 |

4000 |

500 |

3500

( 3 rows)

此例中将 name 列中的值转换为列并计算了他的聚合值生成一个新的结果集。

多列转换的的例子：

select * from

(select month,name,nums,other from pivot_t1 ) pivot ( sum (nums) for ␣

, → (name,other) in ( ( 'apple' , 'a' ), ( 'orange' , 'b' ) , ( 'grape' , 'c' ) , ( 'apple' , 'd' ) , (

, → 'orange' , 'd' )));

month | apple_a | orange_b | grape_c | apple_d | orange_d

-------+---------+----------+---------+---------+----------

1 |

1000 |

|

3500 |

|

400

2 |

|

500 |

3500 |

2000 |

3 |

4000 |

500 |

3500 |

|

此例中将 name 列和 other 列中的值转换为列并计算了他的聚合值生成一个新的结果集。

9.2 UNPIVOT 列转行

UNPIVOT 是 PIVOT 的反操作，它是将列转换为行。

SELECT select_list

FROM table_expression

[ UNPIVOT new_column ... FOR new_column ... IN ((column, ... ) [AS alais], .... ) ]

UNPIVOT 将列旋转为行，生成的交叉表中包含：未出现在 UNPIVOT 的 IN 子句中的所有列，以及存储参

与转换字段列名的新列（ FOR 子句之后指定）和对应输出字段值的新列（ FOR 子句前指定）。 INCLUDE |

EXCLUDE NULLS 子句提供了包括或排除空值行的选项。 INCLUDE NULLS 使 unpivot 操作包含 null 值的行；

EXECLUDE NULLS 将排除具有 null 值的行。如果省略此子句，则 unpivot 操作将排除 null 值。 FOR 子句此

子句指定转换后的新字段名称，用于存放参与列转行的每个输出字段的列名。 IN 子句指定要参与行列转换

的原始列表，指定名称将成为 IN 子句指定新列的输入数据（列转行的值列表）。可选 AS 子句允许输入数据

列名映射到其他指定文字。

一个转换单列的例子 :

select * from unpivot_t1 ;

id |

name

|

q1

|

q2

|

q3

|

q4

----+--------+------+------+------+------

1 | apple

| 1000 | 2000 | 3300 | 5000

2 | orange | 3000 | 3000 | 3200 | 1500

3 | banana | 2500 | 3500 | 2200 | 2500

4 | grape

| 1500 | 2500 | 1200 | 3500

( 4 rows)

update unpivot_t1 set q4 = NULL where id = 4 ;

select id ,name,jidu ,xiaoshou from unpivot_t1 unpivot include nulls (xiaoshou for ␣

, → jidu in (q1 as 'Q1' ,q2 as 'Q2' ,q3 as 'Q3' ,q4 as 'Q4' )) order by id ,jidu;

id |

name

| jidu | xiaoshou

----+--------+------+----------

1 | apple

| Q1

|

1000

1 | apple

| Q2

|

2000

1 | apple

| Q3

|

3300

(continues on next page)

(continued from previous page)

1 | apple

| Q4

|

5000

2 | orange | Q1

|

3000

2 | orange | Q2

|

3000

2 | orange | Q3

|

3200

2 | orange | Q4

|

1500

3 | banana | Q1

|

2500

3 | banana | Q2

|

3500

3 | banana | Q3

|

2200

3 | banana | Q4

|

2500

4 | grape

| Q1

|

1500

4 | grape

| Q2

|

2500

4 | grape

| Q3

|

1200

4 | grape

| Q4

|

( 16 rows)

多列转换的的例子：

select * from unpivot_t1 unpivot ((xiaoshou,xiaoshou2) for (jidu1,jidu2) in ((q1,q2),

, → (q3,q4))) order by 1 , 2 , 3 ;

id |

name

| jidu1 | jidu2 | xiaoshou | xiaoshou2

----+--------+-------+-------+----------+-----------

1 | apple

| q1_q2 | q1_q2 |

1000 |

2000

1 | apple

| q3_q4 | q3_q4 |

3300 |

5000

2 | orange | q1_q2 | q1_q2 |

3000 |

3000

2 | orange | q3_q4 | q3_q4 |

3200 |

1500

3 | banana | q1_q2 | q1_q2 |

2500 |

3500

3 | banana | q3_q4 | q3_q4 |

2200 |

2500

4 | grape

| q1_q2 | q1_q2 |

1500 |

2500

4 | grape

| q3_q4 | q3_q4 |

1200 |

( 8 rows)