oracle 行转列、列转行

最近做数据处理,经常遇到需要行转列、列转行的场景,记录个非常简单实用的oracle  列转行、行转的列方法

1、行转列,基础数据如下

oracle 行转列、列转行

做行转列处理 处理SQL

select user_name,max(date_201501) as date_201501,max(date_201502),max(date_201503),max(date_201504) from
(select t.user_name,
case when t.acct_date = '201501' then t.flow end as date_201501,
case when t.acct_date = '201502' then t.flow end as date_201502,
case when t.acct_date = '201503' then t.flow end as date_201503,
case when t.acct_date = '201504' then t.flow end as date_201504 from DATD_TEST t) t1 group by user_name;

处理结果:

oracle 行转列、列转行

2、列转行,基础数据如上

列转行处理SQL:

select user_name,'201501' as acct_name ,date_201501 as flow from DATD_TEST2
union all
select user_name,'201502' as acct_name,date_201502 as flow from DATD_TEST2
union all
select user_name,'201503' as acct_name,date_201503 as flow from DATD_TEST2
union all
select user_name,'201504' as acct_name,date_201504 as flow from DATD_TEST2 order by user_name;

处理结果:

oracle 行转列、列转行

问题解决~~~~

上一篇:sodu 命令场景分析


下一篇:[转]Oracle SQL函数pivot、unpivot转置函数实现行转列、列转行