首先创建测试环境
create table test1(
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 test1
values
('Rjbryla',
to_date('2009-08-21', 'yyyy-mm-dd'),
'rjbryla@example.com',
'rjbdba@example.com',
'pensivepenman@example.com',
'unclebob@example.com');
insert into test1
values
('johndoe',
to_date('2009-08-22', 'yyyy-mm-dd'),
'janedoe@example.com',
null,
'dog@example.com',
null);
--查询表中的内容
select user_account, signup_date, src_col_name, friend_email
from test1 unpivot((friend_email) for src_col_name in(user_email,
friend1_email,
friend2_email,
friend3_email));
该语句的作用就是将列转成行,src_col_name.friend_email是临时变量,unpivot(聚合函数 for 列名 in(类型)) 是基本语法 ,其中 in(‘’) 中可以指定别名,in中还可以指定子查询;该语句查询出来的结果如下图所示:
oracle11g之前的sql语句为:
select user_account,signup_date,'USER_EMAIL' as src_col_name,user_email as friend_email from test1
where user_email is not null
union
select user_account,signup_date,'FRIEND1_EMAIL' as src_col_name,user_email as friend_email from test1
where friend1_email is not null
union
select user_account,signup_date,'FRIEND2_EMAIL' as src_col_name,user_email as friend_email from test1
where friend2_email is not null
union
select user_account,signup_date,'FRIEND3_EMAIL' as src_col_name,user_email as friend_email from test1
where friend3_email is not null;