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

--创建测试表

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);

--查询表中的内容



--需求：需要将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));



--在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/