Oracle之行列转换

行转列

Pivot

语法：
　　SELECT …
　　FROM …
　　PIVOT [XML]
　　( pivot_clause
　　　　pivot_for_clause
　　　　pivot_in_clause )
　　WHERE …
　　--说明：pivot（聚合函数 for 列名 in（类型）），其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct ranking from temp
实例：

SELECT * FROM emp ;

		JOB           DEPTNO       SAL
		--------- ---------- ----------
		ANALYST           20       6600
		CLERK             10       1430
		CLERK             20       2090
		CLERK             30       1045
		MANAGER           10       2695
		MANAGER           20     3272.5
		MANAGER           30       3135
		PRESIDENT         10       5500
		SALESMAN          30       6160
		
		
SELECT * FROM EMP
PIVOT (
		SUM(sal)        --<-- pivot_clause
	FOR deptno          --<-- pivot_for_clause
	IN  (10 AS 'A部',20 AS 'B部',30 AS 'C部',40 AS 'D部')   --<-- pivot_in_clause
	);
	
		JOB           A部         B部         C部         D部
		--------- ---------- ---------- ---------- ----------
		CLERK           1430       2090       1045
		SALESMAN                              6160
		PRESIDENT       5500
		MANAGER         2695     3272.5       3135
		ANALYST                    6600

max&decode、max&case when

实例：

----------------------------------------------------------------------------------------------------			
max&decode
	--说明：decode的用法：decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
	with temp as(
	select '四川省' nation ,'成都市' city,'第一' ranking from dual union all 
	select '四川省' nation ,'绵阳市' city,'第二' ranking from dual union all 
	select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all 
	select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all 
	select '湖北省' nation ,'武汉市' city,'第一' ranking from dual union all 
	select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all 
	select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual 
	)
	select nation,
	max(decode(ranking, '第一', city, '')) as 第一,
	max(decode(ranking, '第二', city, '')) as 第二,
	max(decode(ranking, '第三', city, '')) as 第三,
	max(decode(ranking, '第四', city, '')) as 第四
	from temp group by nation;		
----------------------------------------------------------------------------------------------------	
max&case when	
	select case when grade_id='1' then '一年级'
	when grade_id='2' then '二年级'
	when grade_id='5' then '五年级' 
	else null end "年级",
	max(case when subject_name='语文'  then max_score
	else 0 end) "语文" ,
	max(case when subject_name='数学'  then max_score
	else 0 end) "数学" ,
	max(case when subject_name='政治'  then max_score
	else 0 end) "政治"
	from dim_ia_test_ysf
	group by
	case when grade_id='1' then '一年级'
	when grade_id='2' then '二年级'
	when grade_id='5' then '五年级' 
	else null end

列转行

unpivot

语法：
　　SELECT …
　　FROM …
　　UNPIVOT [INCLUDE|EXCLUDE NULLS]
　　( unpivot_clause
　　　　unpivot_for_clause
　　　　unpivot_in_clause )
　　WHERE …

实例：

with temp as(
	select '四川省' nation ,'成都市' num1,'绵阳市' num2,'德阳市' num3,'宜宾市' num4  from dual union all 
	select '湖北省' nation ,'武汉市' num1,'宜昌市' num2,'襄阳市' num3,'' num4   from dual 
)
select nation,name,title from temp
unpivot
(     name 
  for title 
  in (num1,num2,num3,num4)
)

UNION/UNION ALL

实例：

with temp as(
	select '四川省' nation ,'成都市' num1,'绵阳市' num2,'德阳市' num3,'宜宾市' num4  from dual union all 
	select '湖北省' nation ,'武汉市' num1,'宜昌市' num2,'襄阳市' num3,'' num4   from dual 
)
select nation,num1 AS NAME , 'num1' AS TITLE FROM TEMP UNION ALL 
select nation,num2 AS NAME , 'num2' AS TITLE FROM TEMP UNION ALL
select nation,num3 AS NAME , 'num3' AS TITLE FROM TEMP UNION ALL
select nation,num4 AS NAME , 'num4' AS TITLE FROM TEMP ;