如何在 Oracle 中对结果进行 UNPIVOT?
问题:
您想在 Oracle 中对结果进行 UNPIVOT。
解决方案
UNPIVOT 子句是 Oracle Database 11g 的新增功能，它允许您将查询输出中的列翻转为行，同时允许您对数据运行聚合函数。
假设有一个名为 customer 的表，其中包含以下数据。
示例
SELECT * FROM customers;
输出
1 tammy.bryant@internalmail Tammy Bryant 2 roy.white@internalmail Roy White 3 gary.jenkins@internalmail Gary Jenkins 4 victor.morris@internalmail Victor Morris 5 beverly.hughes@internalmail Beverly Hughes
在客户表中，我们可以看到全名有名字和姓氏，现在让我们将名字和姓氏分成不同的列。
示例
SELECT full_name, SUBSTR(full_name,1,INSTR(full_name,' ',1,1)-1) first_name, SUBSTR(full_name,INSTR(full_name,' ',-1) + 1) last_name FROM customers;
输出
Tammy Bryant Tammy Bryant Roy White Roy White Gary Jenkins Gary Jenkins Victor Morris Victor Morris Beverly Hughes Beverly Hughes Evelyn Torres Evelyn Torres
现在，如果我们想将这些名字和姓氏列转换为一个单独的列，我们可以使用 oracle 的 UNPIVOT 函数。
示例
SELECT DISTINCT new_column FROM ( SELECT full_name, SUBSTR(full_name,1,INSTR(full_name,' ',1,1)-1) first_name, SUBSTR(full_name,INSTR(full_name,' ',-1) + 1) last_name FROM customers ) UNPIVOT (new_column FOR ref_col2 IN (first_name,last_name));
输出
Roy Beverly Carl Sanchez Evans Martinez Dennis Brown Deborah Carolyn Bennett Jack
数据准备:用于解决该问题的数据如下所示。
示例
create table customers ( customer_id integer generated by default on null as identity, email_address varchar2(255 char) not null, full_name varchar2(255 char) not null) ; insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (1,'tammy.bryant@internalmail','Tammy Bryant'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (2,'roy.white@internalmail','Roy White'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (3,'gary.jenkins@internalmail','Gary Jenkins'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (4,'victor.morris@internalmail','Victor Morris'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (5,'beverly.hughes@internalmail','Beverly Hughes'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (6,'evelyn.torres@internalmail','Evelyn Torres'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (7,'carl.lee@internalmail','Carl Lee'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (8,'douglas.flores@internalmail','Douglas Flores'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (9,'norma.robinson@internalmail','Norma Robinson'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (10,'gregory.sanchez@internalmail','Gregory Sanchez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (11,'judy.evans@internalmail','Judy Evans'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (12,'jean.patterson@internalmail','Jean Patterson'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (13,'michelle.ramirez@internalmail','Michelle Ramirez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (14,'elizabeth.martinez@internalmail','Elizabeth Martinez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (15,'walter.rogers@internalmail','Walter Rogers'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (16,'ralph.foster@internalmail','Ralph Foster'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (17,'tina.simmons@internalmail','Tina Simmons'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (18,'peter.jones@internalmail','Peter Jones'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (19,'kathryn.rogers@internalmail','Kathryn Rogers'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (20,'dennis.lopez@internalmail','Dennis Lopez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (21,'martha.baker@internalmail','Martha Baker'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (22,'raymond.bailey@internalmail','Raymond Bailey'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (23,'christopher.allen@internalmail','Christopher Allen'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (24,'jonathan.coleman@internalmail','Jonathan Coleman'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (25,'walter.turner@internalmail','Walter Turner'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (26,'anna.murphy@internalmail','Anna Murphy'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (27,'carol.alexander@internalmail','Carol Alexander'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (28,'teresa.brown@internalmail','Teresa Brown'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (29,'beverly.rivera@internalmail','Beverly Rivera'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (30,'lisa.hughes@internalmail','Lisa Hughes'); COMMIT;
