一、PIVOT和UNPIVOT行列转换
PIVOT 和 UNPIVOT 是SELECT的一种扩展查询方式。 PIVOT 查询涉及将行置转换为列，或者使用 UNPIVOT 将列置转换为行，以交叉表格式生成结果。
PIVOT 操作为行转列，并在转换过程中聚合数据，新的列表示不同范围的聚合数据， PIVOT 操作的输出通常比初始数据行包含更多的列和更少的行。
UNPIVOT 是 PIVOT 的反操作，它是将多个列的内容合并到同一个列中，以多行形式展示。
关于输入结果有如下使用限制：
输入为一个查询或者子查询的结果集，当为一个子查询的时候，支持目标列的类型可以为列名、常量或者表达式，但是为常量或者表达式的时候必须使用别名，表达式中支持使用列名和常量进行计算，且子查询只支持单表为输入且不支持表使用别名。
1.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子句中指定的一组值。
根据指定的分组列和聚合值，生成的交叉表包含以下列：
所有没在PIVOT子句中引用的隐式分组列值。
以及与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
-------+---------+------+-------
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列中的值转换为列并计算了他的聚合值生成一个新的结果集。
1.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
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)
二、pivot连续使用两次
最近在项目上碰到oracle->kingbase改造中视图里面使用了两次pivot
下面测试是否可以使用两次将第一次pivot的结果第二次pivot引用
pivot行转列测试连续使用两个pviot
20240731
---构建数据
drop table usr;
create table usr(
id int,
name varchar2(20),
score int,
class varchar2(20)
);
insert into usr values(1,'a',20,'math');
insert into usr values(1,'a',22,'phy');
insert into usr values(2,'b',23,'phy');
insert into usr values(3,'b',21,'math');
insert into usr values(2,'c',22,'phy');
insert into usr values(3,'c',24,'math');
insert into usr values(1,'d',25,'math');
insert into usr values(2,'d',23,'phy');
test=# select * from usr;
id | name | score | class
----+------+-------+-------
1 | a | 20 | math
1 | a | 22 | phy
2 | b | 23 | phy
3 | b | 21 | math
2 | c | 22 | phy
3 | c | 24 | math
1 | d | 25 | math
2 | d | 23 | phy
(8 rows)
--执行报错SQL 错误 [0A000]: ERROR: We don't support pivot with sub-query which contains a '*' in it's targets.
select * from (
select * from usr pivot(sum(score)for class in ('math','phy'))) pivot(sum(phy) for id in(1,2,3));
---可以执行
select * from (
select id,name,math,phy from usr pivot(sum(score)for class in ('math','phy'))) pivot(sum(phy) for id in(1,2,3));
---问题出在这里：不可以使用*，要使用具体第一次pivot之后的字段
<in ('math','phy'))) pivot(sum(phy) for id in(1,2,3));
name | math | 1 | 2 | 3
------+------+----+----+---
a | 20 | 22 | |
b | 21 | | |
b | | | 23 |
c | 24 | | |
c | | | 22 |
d | 25 | | |
d | | | 23 |
(7 rows)
--构建新表查看字段
select * into table t3 from usr pivot(sum(score)for class in ('math','phy'));
--查询结果
select * from t3;
id | name | math | phy
----+------+------+-----
1 | a | 20 | 22
1 | d | 25 |
2 | b | | 23
2 | c | | 22
2 | d | | 23
3 | b | 21 |
3 | c | 24 |
(7 rows)
最终需要的结果是
最终结果是
name | math | 1 | 2 | 3
------+------+----+----+---
a | 20 | 22 | |
b | 21 | | |
b | | | 23 |
c | 24 | | |
c | | | 22 |
d | 25 | | |
d | | | 23 |
(7 rows)
正确，可以使用两次，但是不支持嵌套*
扫一扫
911
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
