如何在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;