oracle pivot/unpivot 查询

转置(pivot) 是一种常见的书写交叉表的查询技术，可以将数据行转置为数据的聚集列。

pivot的用法：

未使用pivot之前的转置写法如下：

select * 
	from (select job,sum(decode(deptno,10,sal)) dept10,
									 sum(decode(deptno,20,sal)) dept20,
									 sum(decode(deptno,30,sal)) dept30,
									 sum(decode(deptno,40,sal)) dept40
				from emp
				group by job)
order by job;	

使用pivot之后的写法更加优雅，如下：

select * 
	from (select job,deptno,sum(sal) from emp group by job,deptno)
	pivot(sum(sal) for deptno in (10,20,30,40))
order by job;	

pivot 的语法如下：

SELECT ...
FROM   ...
PIVOT (
    pivot_clause           #定义进行聚集的列
    pivot_for_clause       #定义需要分组和转置的列
    pivot_in_clause        #定义限定结果集的值的范围
)
WHERE  ...

unpivot的用法：

语法示例：

SELECT ...
FROM   ...
UNPIVOT (
    unpivot_clause           #定义反转置值后的列名称
    unpivot_for_clause       #定义反转置查询所得到列的列名称
    unpivot_in_clause        #定义进行反转置的已转置列的列表
)
WHERE  ...

SQL查询示例：

create table pivot_tab as 
    select *  from 
    (select job,deptno,sal from emp)
    pivot (sum(sal) as sum_sal for deptno in (
    10 as dept10,
    20 as dept20,
    30 as dept30,
    40 as dept40
    ));

select * from pivot_tab
	unpivot(sal_amt 
    for deptsal_desc 
    in (dept10_sum_sal,dept20_sum_sal,dept30_sum_sal,dept40_sum_sal)
    )
order by job;