Oracle 行列互转

一、行转列

1）decode方式

with courses as(

select'001' user_id ,'张三' user_name,'语文' course,67 score from dual unionall

select'001' user_id ,'张三' user_name,'数学' course,76 score from dual unionall

select'001' user_id ,'张三' user_name,'英语' course,100 score from dual unionall

select'002' user_id ,'李四' user_name,'语文' course,70 score from dual unionall

select'002' user_id ,'李四' user_name,'数学' course,90 score from dual unionall

select'002' user_id ,'李四' user_name,'英语' course,120 score from dual

)

SELECT USER_ID,USER_NAME,

SUM(DECODE(course,'语文',score,0)) 语文,--这里使用max,min都可以

SUM(DECODE(course,'数学',score,0)) 数学,

SUM(DECODE(course,'英语',score,0)) 英语

FROM courses

 

GROUPBY USER_ID ,USER_NAME

 

执行结果：

 

USER_ID	USER_NAME	语文	数学	英语
001	张三	67	76	100
002	李四	70	90	120

 

 2)CASE WHEN 表达式

 

 with courses as(

select'001' user_id ,'张三' user_name,'语文' course,67 score from dual unionall

select'001' user_id ,'张三' user_name,'数学' course,76 score from dual unionall

select'001' user_id ,'张三' user_name,'英语' course,100 score from dual unionall

select'002' user_id ,'李四' user_name,'语文' course,70 score from dual unionall

select'002' user_id ,'李四' user_name,'数学' course,90 score from dual unionall

select'002' user_id ,'李四' user_name,'英语' course,120 score from dual

)

SELECT USER_ID,USER_NAME,

sum(casewhen course='语文'then score end) "语文",

sum(casewhen course='数学'then score end) "数学",

sum(casewhen course='英语'then score end) "英语"

from courses

groupby USER_ID ,USER_NAME

 

二、列转行

 1）UNION ALL

with courses as(

select'001' user_id ,'张三' user_name,67 ch_score,76 math_score,100 eng_score  from dual

union

select'002' user_id ,'李四' user_name,70 ch_score,90 math_score,120 eng_score from dual

)

 

select user_id,user_name,'语文' course,ch_score score from courses

union

select user_id,user_name,'数学' course,math_score score from courses

union

 

select user_id,user_name,'英语' course,eng_score score from courses

查询结果：

 

USER_ID	USER_NAME	COURSE	SCORE
002	李四	数学	90
001	张三	数学	76
002	李四	英语	120
001	张三	英语	100
002	李四	语文	70
001	张三	语文	67

 

2）INSERT ALL

语句如下：

createtable lie2hang_result(

idnumber,

namevarchar2(20),

course varchar2(20),

score number

);

insertall

into lie2hang_result(id,name,course,score) values(id,name,'语文',chinese)

into lie2hang_result(id,name,course,score) values(id,name,'数学',math)

into lie2hang_result(id,name,course,score) values(id,name,'英语',english)

into lie2hang_result(id,name,course,score) values(id,name,'历史',history)

into lie2hang_result(id,name,course,score) values(id,name,'化学',chemistry)

 

selectid,name,chinese,math,english,history,chemistry from lie2hang;