In practice, we will meet the requirement to rotate the table including convert cols to rows, convert rows to cols. In this session, I’ll produce the solution in common method as well as db-specified features.
Convert columns to rows
1
2
3
4
5
6
7
8
9
10
--common solution
select * from (
select id , 'col_1' as col_name , col_1 as col_val
from table
union all
select id , 'col_2' as col_name , col_2 as col_val
from table
union all
...
) order by id
1
2
3
4
5
6
7
--oracle 11g unpivot query
select *
from table
unpivot (
col_val for col_name in ( col_1 as 'col_1' , col_2 as 'col_2' )
)
order by id
Concert rows to columns
1
2
3
4
5
6
7
--common solution
select id ,
max ( case when col_name = 'col_1' then col_val else null end ) as col_1 ,
max ( case when col_name = 'col_2' then col_val else null end ) as col_2 ,
...
from table
order by id
1
2
3
4
5
6
7
8
--oracle 11g pivot query
select *
from table
pivot (
max ( col_val ) for col_name in ( 'col_1' as col_1 , 'col_2' as col_2 )
)
where ...
order by id
Excel pivot table
For non-sql approaches, we can use excel pivot table. Excel spreadsheets are a great way to pivot and analyze Oracle data, and tools.