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/，如需轉載，請註明出處，否則將追究法律責任。