Oracle 11g使用UNPIVOT函数实现“列转行”

浅浅的花香味﹌ 2024-04-17 23:47 67阅读 0赞

**--创建测试表**  
  
create table email\_signup(  
user\_account varchar2(100),  
signup\_date date,  
user\_email varchar2(100),  
friend1\_email varchar2(100),  
friend2\_email varchar2(100),  
friend3\_email varchar2(100)  
);  
  
insert into email\_signup  
values  
  ('Rjbryla',  
   to\_date('2009-08-21', 'yyyy-mm-dd'),  
   'rjbryla@example.com',  
   'rjbdba@example.com',  
   'pensivepenman@example.com',  
   'unclebob@example.com');  
  
insert into email\_signup  
values  
  ('johndoe',  
   to\_date('2009-08-22', 'yyyy-mm-dd'),  
   'janedoe@example.com',  
   null,  
   'dog@example.com',  
   null);  
  
\--查询表中的内容  
  
![Image 1][]  
  
\--需求：需要将USER\_EMAIL、FRIEND1\_EMAIL、 FRIEND2\_EMAIL、 FRIEND3\_EMAIL都转到一行上  
实现的格式如下：  
USER\_ACCOUNT | SIGNUP\_DATE | EMAIL\_ADDRESS  
  
**--使用UNPIVOT函数实现列转行**  
  
select user\_account, signup\_date, src\_col\_name, friend\_email  
  from email\_signup unpivot((friend\_email) for src\_col\_name in(user\_email,  
                                                               friend1\_email,  
                                                               friend2\_email,  
                                                               friend3\_email));  
  
![Image 1][]  
  
**--在11g之前，可以使用下面的方法**  
  
select user\_account,signup\_date,'USER\_EMAIL' as src\_col\_name,user\_email as friend\_email from email\_signup  
where user\_email is not null  
union  
select user\_account,signup\_date,'FRIEND1\_EMAIL' as src\_col\_name,user\_email as friend\_email from email\_signup  
where friend1\_email is not null  
union  
select user\_account,signup\_date,'FRIEND2\_EMAIL' as src\_col\_name,user\_email as friend\_email from email\_signup  
where friend2\_email is not null  
union  
select user\_account,signup\_date,'FRIEND3\_EMAIL' as src\_col\_name,user\_email as friend\_email from email\_signup  
where friend3\_email is not null;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2057034/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2057034/

[Image 1]: