最近做数据处理,经常遇到需要行转列、列转行的场景,记录个非常简单实用的oracle 列转行、行转的列方法
1、行转列,基础数据如下
做行转列处理 处理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;
处理结果:
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;
处理结果:
问题解决~~~~