加载中…Oracle中行转列、列转行的实现方法详解
(2017-04-24 11:00:01)
标签：
oracle行转列方法oracle列转行方法pivot函数用法示例oracle行列转换示例unpivot函数用法示例 |
分类： Oracle数据库 |
一、创建测试表
drop table t_test_fee;
create table t_test_fee(
pno
integer,
p_code varchar2(30),
p_cost number
)
insert into t_test_fee(pno,p_code,p_cost)
select 100001,'010',89.03 from dual
union all
select 100001,'011',103.01 from dual
union all
select 100001,'019',58.08 from dual
union all
select 100001,'047',94.32 from dual
union all
select 100001,'055',61.74 from dual
union all
select 100001,'068',79.81 from dual
union all
select 100001,'011',100.39 from dual
;
commit;
select * from t_test_fee
二、行转列的方法
2.1、通过 decode 函数实现行转列，适用范围：oracle
8i，9i，10g及以后版本
select pno,
sum(decode(p_code, '010', p_cost)) "010",
sum(decode(p_code, '011', p_cost)) "011",
sum(decode(p_code, '019', p_cost)) "019",
sum(decode(p_code, '047', p_cost)) "047",
sum(decode(p_code, '055', p_cost)) "055",
sum(decode(p_code, '068', p_cost)) "068"
from t_test_fee
group by pno
order by pno asc
2.2、通过 pivot 函数实现行转列，适用范围：oracle
11g及以后
select *
from t_test_fee
) order by pno asc;
注意：如果聚合函数与 PIVOT
一起使用，则计算聚合时将不考虑出现在值列中的任何空值。
2.3、通过动态拼接sql处理实现行转列
declare
begin
from t_test_fee
) order by pno asc';
end;
--这里把查询结果放到了视图tmp_result里，查看这个视图就可以了。
select * from tmp_result;
2.4、通过聚合函数 sum 实现行转列
select pno,sum(case when p_code='010' then p_cost else 0 end)
"010",
sum(case when p_code='011' then p_cost else 0 end)
"011",
sum(case when p_code='019' then p_cost else 0 end)
"019",
sum(case when p_code='047' then p_cost else 0 end)
"047",
sum(case when p_code='055' then p_cost else 0 end)
"055",
sum(case when p_code='068' then p_cost else 0 end) "068"
from t_test_fee
group by pno;
2.5、通过 pivot xml
函数来实现，结果将以xml格式输出。
-- 允许使用 any 关键字
select *
from t_test_fee
) order by pno asc;
-- 允许使用子查询
select *
from t_test_fee
) order by pno asc;
三、列转行的方法
3.1、通过 listagg 函数实现列转行
with tab1 as(
)
select nation,listagg(city,',') within group (order by
city)
from tab1
group by nation
3.2、通过 listagg 和 rank
函数实现行转列
with tab1 as(
)
select population,
from tab1
3.3、通过 wm_concat 函数实现列转行
with tab1 as(
)
select nation,wm_concat(city)
from tab1
group by nation
3.4、通过 union all 函数实现列转行
with tab1 as (
select 23 as cnt1,100 as cnt2,300 as cnt3,500 as cnt4,800 as
cnt5,1024 as cnt6,6721 as
cnt7,890 as cnt8 from dual
)
select cnt1 from tab1
union all
select cnt2 from tab1
union all
select cnt3 from tab1
union all
select cnt4 from tab1
union all
select cnt5 from tab1
union all
select cnt6 from tab1
union all
select cnt7 from tab1
union all
select cnt8 from tab1
;
3.5、通过 unpivot 函数实现列转行
with tab1 as (
select 23 as cnt1,100 as cnt2,300 as cnt3,500 as cnt4,800 as
cnt5,1024 as cnt6,6721 as
cnt7,890 as cnt8 from dual
)
select jk_cnt
from tab1
unpivot (jk_cnt for xm_name in
(cnt1,cnt2,cnt3,cnt4,cnt5,cnt6,cnt7,cnt8))
;
注意：UNPIVOT 并不完全是 PIVOT 的逆操作。
PIVOT 会执行一次聚合，从而将多个可能的行合并为输出中的单个行。 而 UNPIVOT
不会重现原始表值表达式的结果，因为行已经被合并了。 另外，UNPIVOT 的输入中的 null 值不会显示在输出中，而在执行
PIVOT 操作之前，输入中可能有原始的 null 值。
四、总结
本文参考资料：http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html
喜欢
0
赠金笔