--创建测试表
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
& ('Rjbryla',
& &to_date('', 'yyyy-mm-dd'),
insert into email_signup
& ('johndoe',
& &to_date('', 'yyyy-mm-dd'),
--查询表中的内容
--需求：需要将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
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
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
select user_account,signup_date,'FRIEND3_EMAIL' as src_col_name,user_email as friend_email from email_signup
where friend3_
&&&回复&&&：
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备号 北京市公安局海淀分局网监中心备案编号：10
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员Oracle 11g行列转换之UNPIVOT_数据库技术_Linux公社-Linux系统门户网站
你好，游客
Oracle 11g行列转换之UNPIVOT
来源：Linux社区&
作者：lsmnlsmn
11g提供了新的行列转换操作：PIVOT(行转列)和UNPIVOT列转行。本文对UNPIVOT的功能进行展示。
现有表src_table如下：
product_Id(产品ID)
product_color(颜色)
porduct_type(型号)
is_intelligent(是否智能)
目标表dest_table如下：
product_Id
param_name(参数名称)
param_value(参数值)
product_color
product_color
product_color
product_type
product_type
product_type
is_intelligent
is_intelligent
is_intelligent
通过UNPIVOT实现如下：
SELECT *&& FROM&& src_table&& UNPIVOT (param_value FOR param_name IN (product_color AS 'product_color', product_type AS 'product_type', is_intelligent AS 'is_intelligent');
相关资讯 & & &
& (10/16/:44)
& (04/19/:30)
& (08/24/:41)
& (04/25/:38)
& (11/16/:27)
& (08/19/:58)
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款Oracle 行转列pivot 、列转行unpivot 的Sql语句总结 - _harvey - 博客园
随笔 - 59, 文章 - 3, 评论 - 9, 引用 - 0
多行转字符串
这个比较简单，用||或concat函数可以实现
select&concat(id,username)&str&from&app_user&&
select&id||username&str&from&app_user&&
字符串转多列
实际上就是拆分字符串的问题，可以使用 substr、instr、regexp_substr函数方式
字符串转多行
使用union all函数等方式
首先让我们来看看这个神奇的函数(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备数据
create&table&test(id&number,name&varchar2(20));&&
insert&into&test&values(1,'a');&&
insert&into&test&values(1,'b');&&
insert&into&test&values(1,'c');&&
insert&into&test&values(2,'d');&&
insert&into&test&values(2,'e');&&
效果1 : & 行转列 &，默认逗号隔开
select&wm_concat(name)&name&from&&&
效果2: & 把结果里的逗号替换成"|"
select&replace(wm_concat(name),',','|')&from&&&
效果3: &按ID分组合并name
select&id,wm_concat(name)&name&from&test&group&by&&&
sql语句等同于下面的sql语句
select&id,&max(decode(rn,&1,&name,&null))&||&max(decode(rn,&2,&','||name,&null))&||&max(decode(rn,&3,&','||name,&null))&str&&
&&&&from&(select&id,&name&,row_number()&over(partition&by&id&order&by&name)&as&rn&from&test)&t&group&by&id&order&by&1;&&&
select&id,&str&from&(select&id,row_number()&over(partition&by&id&order&by&name)&as&rn,name&||&lead(','&||&name,&1)&&
&&&&over(partition&by&id&order&by&name)&||&&lead(','&||&name,&2)&over(partition&by&id&order&by&name)&||&lead(','&||&name,&3)&&&
&&&&over(partition&by&id&order&by&name)&as&str&from&test)&where&rn&=&1&order&by&1;&&
select&id,&substr(str,&2)&str&from&test&model&return&updated&rows&partition&by(id)&dimension&by(row_number()&&
&&&&over(partition&by&id&order&by&name)&as&rn)&measures&(cast(name&as&varchar2(20))&as&str)&rules&upsert&iterate(3)&&
&&&&until(presentv(str[iteration_number&+&2],&1,&0)=0)&(str[0]&=&str[0]&||&','&||&str[iteration_number&+&1])&order&by&1;&&&&&&&
&&&&&&&&&&&
select&t.id&id,&max(substr(sys_connect_by_path(t.name,&','),&2))&str&from&(select&id,&name,&row_number()&&&
&&&&over(partition&by&id&order&by&name)&rn&from&test)&t&start&with&rn&=&1&connect&by&rn&=&prior&rn&+&1&and&id&=&prior&id&&
&&&&group&by&t.&&
懒人扩展用法:
案例:&我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法?
当然有了,看我如果应用wm_concat来让这个需求变简单，假设我的APP_USER表中有（id,username,password,age）4个字段。查询结果如下
&/**&这里的表名默认区分大小写&*/&&
select&'create&or&replace&view&as&select&'||&wm_concat(column_name)&||&'&from&APP_USER'&sqlStr&&&
&&&&&&&from&user_tab_columns&where&table_name='APP_USER';&&&&
利用系统表方式查询
select&*&from&user_tab_columns&&
在 11g中，Oracle 又增加了2个查询：pivot（行转列）&和unpivot（列转行）
参考：http://blog.csdn/tianlesoftware/article/details/7060306、http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html
google 一下，网上有一篇比较详细的文档：
pivot 列转行
测试数据 (id，类型名称，销售数量)，案例：根据水果的类型查询出一条数据显示出每种类型的销售数量。
create&table&demo(id&int,name&varchar(20),nums&int);&&
insert&into&demo&values(1,&'苹果',&1000);&&
insert&into&demo&values(2,&'苹果',&2000);&&
insert&into&demo&values(3,&'苹果',&4000);&&
insert&into&demo&values(4,&'橘子',&5000);&&
insert&into&demo&values(5,&'橘子',&3000);&&
insert&into&demo&values(6,&'葡萄',&3500);&&
insert&into&demo&values(7,&'芒果',&4200);&&
insert&into&demo&values(8,&'芒果',&5500);&&
分组查询 （当然这是不符合查询一条数据的要求的）
select&name,&sum(nums)&nums&from&demo&group&by&name&&
行转列查询
select&*&from&(select&name,&nums&from&demo)&pivot&(sum(nums)&for&name&in&('苹果'&苹果,&'橘子',&'葡萄',&'芒果'));&&
注意： pivot（聚合函数 for 列名 in（类型）） & ，其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct code from customers
当然也可以不使用pivot函数，等同于下列语句，只是代码比较长，容易理解
select&*&from&（select&sum(nums)&苹果&from&demo&where&name='苹果'）,（select&sum(nums)&橘子&from&demo&where&name='橘子'）,&&
&&&&&&&（select&sum(nums)&葡萄&from&demo&where&name='葡萄'）,（select&sum(nums)&芒果&from&demo&where&name='芒果'）;&&
select&sum(decode(name,'苹果',nums))&苹果,&sum(decode(name,'橘子',nums))&橘子,&&&
&&&&&&&sum(decode(name,'葡萄',nums))&葡萄,&sum(decode(name,'芒果',nums))&芒果&from&demo&&
unpivot 行转列
顾名思义就是将多列转换成1列中去
案例：现在有一个水果表，记录了4个季度的销售数量，现在要将每种水果的每个季度的销售情况用多行数据展示。
创建表和数据
create&table&Fruit(id&int,name&varchar(20),&Q1&int,&Q2&int,&Q3&int,&Q4&int);&&
insert&into&Fruit&values(1,'苹果',00,5000);&&
insert&into&Fruit&values(2,'橘子',00,1500);&&
insert&into&Fruit&values(3,'香蕉',00,2500);&&
insert&into&Fruit&values(4,'葡萄',00,3500);&&
select&*&from&Fruit&&
列转行查询
select&id&,&name,&jidu,&xiaoshou&from&Fruit&unpivot&(xiaoshou&for&jidu&in&(q1,&q2,&q3,&q4)&)&&
注意： &unpivot没有聚合函数，xiaoshou、jidu字段也是临时的变量
同样不使用unpivot也可以实现同样的效果，只是sql语句会很长，而且执行速度效率也没有前者高
select&id,&name&,'Q1'&jidu,&(select&q1&from&fruit&where&id=f.id)&xiaoshou&from&Fruit&f&&
select&id,&name&,'Q2'&jidu,&(select&q2&from&fruit&where&id=f.id)&xiaoshou&from&Fruit&f&&
select&id,&name&,'Q3'&jidu,&(select&q3&from&fruit&where&id=f.id)&xiaoshou&from&Fruit&f&&
select&id,&name&,'Q4'&jidu,&(select&q4&from&fruit&where&id=f.id)&xiaoshou&from&Fruit&f&&
此处省略部分内容，完整内容请查看原文：多行转字符串
这个比较简单，用||或concat函数可以实现
select concat(id,username) str from app_userselect id||username str from app_user字符串转多列
实际上就是拆分字符串的问题，可以使用 substr、instr、regexp_substr函数方式
字符串转多行
使用union all函数等方式
wm_concat函数
首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据
create table test(id number,name varchar2(20));insert into test values(1,'a');insert into test values(1,'b');insert into test values(1,'c');insert into test values(2,'d');insert into test values(2,'e');效果1 : 行转列 ，默认逗号隔开
select wm_concat(name) name from
效果2: 把结果里的逗号替换成"|"
select replace(wm_concat(name),',','|') from
效果3: 按ID分组合并name
select id,wm_concat(name) name from test group by
sql语句等同于下面的sql语句:
123456789101112131415161718192021222324252627282930313233343536
-------- 适用范围：8i,9i,10g及以后版本
（ MAX + DECODE ）select id,
max(decode(rn, 1, name, null)) ||
max(decode(rn, 2, ',' || name, null)) ||
max(decode(rn, 3, ',' || name, null)) str
from (select id,
row_number() over(partition by id order by name) as rn
from test) t group by id order by 1;-------- 适用范围：8i,9i,10g及以后版本 （ ROW_NUMBER + LEAD ）select id, str
from (select id,
row_number() over(partition by id order by name) as rn,
name || lead(',' || name, 1) over(partition by id order by name) ||
lead(',' || name, 2) over(partition by id order by name) ||
lead(',' || name, 3) over(partition by id order by name) as str
from test) where rn = 1 order by 1;-------- 适用范围：10g及以后版本 （ MODEL ）select id, substr(str, 2) str
from test model return updated rows partition by(id) dimension by(row_number()
over(partition by id order by name) as rn) measures(cast(name as varchar2(20)) as str)
rules upsert iterate(3) until(presentv(str [ iteration_number + 2 ], 1, 0) = 0)
(str [ 0 ] = str [ 0 ] || ',' || str [ iteration_number + 1 ]) order by 1;-------- 适用范围：8i,9i,10g及以后版本 （ MAX + DECODE ）select t.id id, max(substr(sys_connect_by_path(t.name, ','), 2)) str
from (select id, name, row_number() over(partition by id order by name) rn
from test) t start with rn = 1connect by rn = prior rn + 1
and id = prior id group by t.
懒人扩展用法:
案例: 我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单，假设我的APP_USER表中有（id,username,password,age）4个字段。查询结果如下
/** 这里的表名默认区分大小写 */select 'create or replace view as select ' || wm_concat(column_name) ||
' from APP_USER' sqlStr
from user_tab_columns where table_name = 'APP_USER';
利用系统表方式查询
select * from user_tab_columnsOracle 11g 行列互换 pivot 和 unpivot 说明
在Oracle 11g中，Oracle 又增加了2个查询：pivot（行转列） 和unpivot（列转行）
参考：http://blog.csdn.net/tianlesoftware/article/details/7060306、http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.
google 一下，网上有一篇比较详细的文档：http://www.oracle-developer.net/display.php?id=506
pivot 列转行
测试数据 (id，类型名称，销售数量)，案例：根据水果的类型查询出一条数据显示出每种类型的销售数量。
create table demo(id int,name varchar(20),nums int);
---- 创建表insert into demo values(1, '苹果', 1000);insert into demo values(2, '苹果', 2000);insert into demo values(3, '苹果', 4000);insert into demo values(4, '橘子', 5000);insert into demo values(5, '橘子', 3000);insert into demo values(6, '葡萄', 3500);insert into demo values(7, '芒果', 4200);insert into demo values(8, '芒果', 5500);
分组查询 （当然这是不符合查询一条数据的要求的）
select name, sum(nums) nums from demo group by name
行转列查询
select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果'));
注意： pivot（聚合函数 for 列名 in（类型）） ，其中 in('') 中可以指定别名，in中还可以指定子查询，比如 select distinct code from customers
当然也可以不使用pivot函数，等同于下列语句，只是代码比较长，容易理解
from (select sum(nums) 苹果 from demo where name = '苹果'),
(select sum(nums) 橘子 from demo where name = '橘子'),
(select sum(nums) 葡萄 from demo where name = '葡萄'),
(select sum(nums) 芒果 from demo where name = '芒果');unpivot 行转列
顾名思义就是将多列转换成1列中去案例：现在有一个水果表，记录了4个季度的销售数量，现在要将每种水果的每个季度的销售情况用多行数据展示。
创建表和数据
create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);insert into Fruit values(1,'苹果',1000,2000,3300,5000);insert into Fruit values(2,'橘子',3000,3000,3200,1500);insert into Fruit values(3,'香蕉',2500,3500,2200,2500);insert into Fruit values(4,'葡萄',1500,2500,1200,3500);select * from Fruit
列转行查询
select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )注意： unpivot没有聚合函数，xiaoshou、jidu字段也是临时的变量
同样不使用unpivot也可以实现同样的效果，只是sql语句会很长，而且执行速度效率也没有前者高
select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q2' jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q3' jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q4' jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit fXML类型
上述pivot列转行示例中，你已经知道了需要查询的类型有哪些，用in()的方式包含，假设如果您不知道都有哪些值，您怎么构建查询呢？
pivot 操作中的另一个子句 XML 可用于解决此问题。该子句允许您以 XML 格式创建执行了 pivot 操作的输出，在此输出中，您可以指定一个特殊的子句 ANY 而非文字值
示例如下：
select * from (
select name, nums as "Purchase Frequency"
from demo t)
pivot xml (
sum(nums) for name in (any))
如您所见，列 NAME_XML 是 XMLTYPE，其中根元素是 &PivotSet&。每个值以名称-值元素对的形式表示。您可以使用任何 XML 分析器中的输出生成更有用的输出。
对于该xml文件的解析，贴代码如下：
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051 create or replace procedure ljz_pivot_xml_sp(pi_table_name
pi_column_name
pi_create_table varchar2) as
nvarchar2(50);
number := 0;
v_parent_node nvarchar2(4000);
v_child_node
nvarchar2(4000);
boolean := false;
nvarchar2(50);
v_existsnode
varchar2(30);
v_name_begin
:= 'select x.* from ' || pi_table_name ||
' a, xmltable(''/PivotSet'' passing a.' ||
pi_column_name || ' columns ';
v_parent_node := '/PivotSet';
v_child_node
:= 'item[1]/column[2]';
execute immediate 'select ' || pi_column_name || ' from ' ||
pi_table_name || ' where rownum=1'
into v_name_
select existsnode(v_name_xml,
'/PivotSet/item[' || to_char(v_i) || ']/column[1]')
into v_existsnode
while v_existsnode = 1 loop
execute immediate 'select substr(extractvalue(' || pi_column_name ||
', ''/PivotSet/item[' || to_char(v_i) || ']/column[1]''),1,30)
from ' || pi_table_name || ' x'
v_sql := v_sql || '"' || v_name || '" varchar2(30) path ''item[' ||
to_char(v_i) || ']/column[2]'',';
:= v_i + 1;
select existsnode(v_name_xml,
'/PivotSet/item[' || to_char(v_i) || ']/column[1]')
into v_existsnode
v_sql := trim(',' from v_sql) || ') x';
select count(1)
into v_count
from user_tab_columns
where table_name = upper(pi_create_table);
if v_count = 0 then
execute immediate 'create table ' || pi_create_table || ' as ' || v_
end if;end;
第一个参数为要解析xml文件所属数据表，第二个参数为要解析xml所存字段，第三个参数存放解析后的数据集。
ljz_pivot_xml_sp
'deptno_xml'
'ljz_pivot_tmp'end
初学oracle xml解析，这种方法较为笨拙，一个一个循环列，原型如下：
extractvalue
existsnode
不知是否存在直接进行解析的方法，这种方法还不如直接行列转变，不通过xml转来转去。
in苹果'橘子'葡萄'芒果'
这样拼接字符串反而更加方便。
Pivot 为 SQL 语言增添了一个非常重要且实用的功能。您可以使用 pivot 函数针对任何关系表创建一个交叉表报表，而不必编写包含大量 decode 函数的令人费解的、不直观的代码。同样，您可以使用 unpivot 操作转换任何交叉表报表，以常规关系表的形式对其进行存储。Pivot 可以生成常规文本或 XML 格式的输出。如果是 XML 格式的输出，您不必指定 pivot 操作需要搜索的值域。
阅读(...) 评论()Oracle的数据表中行转列与列转行的操作实例讲解
&更新时间：日 17:19:41 & 作者：真正男子汉
这篇文章主要介绍了Oracle数据表中行转列与列转行的操作方法,这里分静态和动态情况作出了分类讨论,需要的朋友可以参考下
查询结果为
select years,(select amount from Tb_Amount as A where month=1 and A.years=Tb_Amount.years)as m1,
(select amount from Tb_Amount as A where month=2 and A.years=Tb_Amount.years)as m2,
(select amount from Tb_Amount as A where month=3 and A.years=Tb_Amount.years)as m3
from Tb_Amount group by years
select years as 年份,
sum(case when month='1' then amount end) as 一月,
sum(case when month='2' then amount end) as 二月,
sum(case when month='3' then amount end) as 三月
from dbo.Tb_Amount group by years order by years desc
2.人员信息表包括姓名 时代& 金额
显示行转列
姓名&&&& 时代&&&&&& 金额
姓名& 年轻&&&&&&&& 中年&&&&&& 老年
孙子 && & .00
select uname as 姓名,
SUM(case when era='年轻' then amount end) as 年轻,
SUM(case when era='中年' then amount end) as 中年,
SUM(case when era='老年' then amount end) as 老年
from Tb_People group by uname order by uname desc
&3.学生表 [Tb_Student]
静态SQL,指subject只有语文、数学、英语这三门课程。
select sname as 姓名,
max(case Subject when '语文' then grade else 0 end) as 语文,
max(case Subject when '数学' then grade else 0 end) as 数学,
max(case Subject when '英语' then grade else 0 end) as 英语
from dbo.Tb_Student group by sname order by sname desc
--动态SQL,指subject不止语文、数学、英语这三门课程。
declare @sql varchar(8000)
set @sql = 'select sname as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then grade else 0 end) [' + Subject + ']'
from (select distinct Subject from Tb_Student) as a
set @sql = @sql + ' from Tb_Student group by sname order by sname desc'
exec(@sql)
oracle中Decode()函数使用 然后将这些累计求和(sum部分）
select t.sname AS 姓名,
sum(decode(t.subject,'语文',grade,null))语文 ,
sum(decode(t.subject,'数学',grade,null)) 数学,
sum(decode(t.subject,'英语',grade,null)) 英语
from Tb_Student t group by sname order by sname desc
from (select sname,[Course ] ='数学',[Score]=[数学] from Tb_students union all
select sname,[Course]='英语',[Score]=[英语] from Tb_students union all
select sname,[Course]='语文',[Score]=[语文] from Tb_students)t
order by sname,case [Course] when '语文' then 1 when '数学' then 2 when '英语' then 3 end
--列转行的静态方案：UNPIVOT,sql2005及以后版本
SELECT sname,Subject, grade
from dbo.Tb_students
unpivot(grade for Subject in([语文],[数学],[英语]))as up
--列转行的动态方案：UNPIVOT,sql2005及以后版本
--因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行，同样也使用了XML处理。
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Tb_students') and Name not in('sname')
order by Colid
exec('select sname,[Subject],[grade] from Tb_students unpivot ([grade] for [Subject] in('+@s+'))b')
sname,[Subject],[grade]
Tb_students
([grade] for [Subject] in([数学],[英语],[语文]))b
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具