Oracle中行转列、列转行

1、行转列：PIVOT

说明：PIVOT (SUM(聚合值) FOR 待转换的列名 IN (待转换的列名里面的值 转换后列的别名))

with temp as(select '张三' NAME ,'语文' course,98 score from dual union allselect '张三' NAME ,'数学' course,100 score from dual union allselect '张三' NAME ,'物理' course,95 score from dual union allselect '张三' NAME ,'英语' course,110 score from dual union allselect '李四' NAME ,'语文' course,96 score from dual union allselect '李四' NAME ,'数学' course,89 score from dual union allselect '李四' NAME ,'物理' course,93 score from dual union allselect '李四' NAME ,'英语' course,100 score from dual 
)
select * from (select NAME,course,score from temp) pivot (max(score) for course in ('语文','数学','物理','英语'));

 

2、行转列：PIVOT

说明：unpivot（自定义列名/*列的值*/ for 自定义列名/*列名*/ in（列名））

WITH TEMP AS(
SELECT '张三' NAME ,'98' 语文,'100' 数学,'95' 物理,'110' 英语 FROM DUAL UNION ALL
SELECT '李四' NAME ,'96' 语文,'89' 数学,'93' 物理,'100' 英语 FROM DUAL 
)
SELECT NAME,COURSE,SCORE FROM TEMP
UNPIVOT (SCORE FOR COURSE IN (语文,数学,物理,英语))T