ORACLE unpivot 行转列

ORACLE unpivot 行转列

unpivot语法：

-- ORACLE unpivot 行转列    
-- 要求行转列的类型必须一致 要么全部是varchar2 要么全是number  
-- 在某些特定条件下特别有用    

-- 示例1 
SELECT TYPE,COURSE_MARK FROM 
(SELECT '罗飞' STU_NAME, '2001-2002' TERM, '90' 微积分, '88' 线性代数, '85' 数据结构, '70' 操作系统 FROM DUAL) 
UNPIVOT
(COURSE_MARK FOR TYPE IN(STU_NAME,TERM, 微积分, 线性代数, 数据结构, 操作系统))
; 

-- 示例2 
select type,value from 
(select to_char(EMPNO,9999) EMPNO,ENAME,JOB,to_char(MGR,90000) MGR,to_char(HIREDATE,'yyyy-mm-dd hh24:mi:ss') HIREDATE,to_char(SAL,9990) SAL,to_char(COMM,99990) COMM,to_char(DEPTNO,9990) DEPTNO from scott.emp  )
unpivot
(value for type in (
 EMPNO     as 'EMPNO'
,ENAME     as 'ENAME'
,JOB       as 'JOB'
,MGR       as 'MGR'
,HIREDATE  as 'HIREDATE'
,SAL       as 'SAL'
,COMM      as 'COMM'
,DEPTNO    as 'DEPTNO'
))
/

-- 示例3 
select type,value from 
(select EMPNO,MGR,SAL,COMM,DEPTNO from scott.emp )
unpivot
(value for type in (
 EMPNO    as 'EMPNO'
,MGR      as 'MGR'
,SAL      as 'SALA'
,COMM     as 'COMM'
,DEPTNO   as 'DEPTNO'
))
/

-- 示例4 
select unp_type,unp_value from 
(select unp_value,unp_type from scott.emp
unpivot
(unp_value for unp_type in (
 EMPNO    as 'EMPNO'
,MGR      as 'MGR'
,SAL      as 'SALA'
,COMM     as 'COMM'
,DEPTNO   as 'DEPTNO'
)) t
) where unp_value between 300 and 5000
/


-- 示例5
select ENAME,unp_type,unp_value from 
(select ENAME,unp_value,unp_type from scott.emp 
unpivot
(unp_value for unp_type in (
 EMPNO    as 'EMPNO'
,MGR      as 'MGR'
,SAL      as 'SALA'
,COMM     as 'COMM'
,DEPTNO   as 'DEPTNO'
)) where ENAME in ('JONES','WARD') 
) where unp_value between 300 and 5000
/

-- 示例5
select ENAME,unp_type,unp_value from 
(select ENAME,unp_type,unp_value from scott.emp 
unpivot
(unp_value for unp_type in (
 EMPNO    as 'EMPNO'
,MGR      as 'MGR'
,SAL      as 'SALA'
,COMM     as 'COMM'
,DEPTNO   as 'DEPTNO'
)) t where ENAME in ('JONES','WARD') and unp_value between 300 and 5000
) 
/


select * from scott.emp where rownum<=1;

学习笔记
参考：https://blog.csdn.net/theory93/article/details/43700047