1、Oracle pivot和unpivot简介
pivot函数:行转列函数:
语法:pivot(任一聚合函数 for 需专列的值所在列名 in (需转为列名的值));
unpivot函数:列转行函数:
语法:unpivot(新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名));
执行原理:将pivot函数或unpivot函数接在查询结果集的后面。相当于对结果集进行处理。
示例:
create table testpivot(姓名 varchar(100),课程 varchar(100),分数 int);
insert into testpivot values (‘张三’,‘语文’,‘74’);
insert into testpivot values (‘张三’,‘语文’,‘75’);
insert into testpivot values (‘张三’,‘数学’,‘83’);
insert into testpivot values (‘张三’,‘物理’,‘93’);
insert into testpivot values (‘李四’,‘语文’,‘74’);
insert into testpivot values (‘李四’,‘数学’,‘84’);
insert into testpivot values (‘李四’,‘物理’,‘94’);
–oracle
select * from testpivot pivot(max(分数) for 课程 in (‘语文’,‘数学’,‘物理’)) a;
CREATE TABLE unpivot_test (
id NUMBER,
customer_id NUMBER,
product_code_a NUMBER,
product_code_b NUMBER,
product_code_c NUMBER,
product_code_d NUMBER
);
INSERT INTO unpivot_test VALUES (1, 101, 10, 20, 30, NULL);
INSERT INTO unpivot_test VALUES (2, 102, 40, NULL, 50, NULL);
INSERT INTO unpivot_test VALUES (3, 103, 60, 70, 80, 90);
INSERT INTO unpivot_test VALUES (4, 104, 100, NULL, NULL, NULL);
select *
from unpivot_test
unpivot include nulls(quantity for product_code in(product_code_a as ‘A’, product_code_b as ‘B’,
product_code_c as ‘C’, product_code_d as ‘D’));