Oracle行列轉換小結

目錄結構如下：
行轉列
列轉行
[一]、行轉列
?
1.1、初始測試數據
?
表結構：TEST_TB_GRADE
?
Sql代碼?
create table TEST_TB_GRADE?
(?
? ID??????? NUMBER(10) not null,?
? USER_NAME VARCHAR2(20 CHAR),?
? COURSE??? VARCHAR2(20 CHAR),?
? SCORE???? FLOAT?
)?
?初始數據如下圖：

???????????????????????
?
1.2、 如果需要實現如下的查詢效果圖：

????????????????????
?
這就是最常見的行轉列，主要原理是利用decode函數、聚集函數(sum)，結合group by分組實現的，具體的sql如下：
Sql代碼?
select t.user_name,?
? sum(decode(t.course, '語文', score,null)) as CHINESE,?
? sum(decode(t.course, '數學', score,null)) as MATH,?
? sum(decode(t.course, '英語', score,null)) as ENGLISH?
from test_tb_grade t?
group by t.user_name?
order by t.user_name?
?
?
1.3、延伸
?
如果要實現對各門功課的不同分數段進行統計，效果圖如下：

????????????????
?
具體的實現sql如下：
Sql代碼?
select t2.SCORE_GP,?
? sum(decode(t2.course, '語文', COUNTNUM,null)) as CHINESE,?
? sum(decode(t2.course, '數學', COUNTNUM,null)) as MATH,?
? sum(decode(t2.course, '英語', COUNTNUM,null)) as ENGLISH?
from (?
? select t.course,?
???????? case when t.score? <60 then '00-60'?
????????????? when t.score >=60 and t.score <80? then '60-80'?
????????????? when t.score >=80 then '80-100' end as SCORE_GP,?
???????? count(t.score) as COUNTNUM?
? FROM test_tb_grade t?
? group by t.course,??
??????? case when t.score? <60? then '00-60'?
????????????? when t.score >=60 and t.score <80? then '60-80'?
????????????? when t.score >=80 then '80-100' end?
? order by t.course ) t2?
group by t2.SCORE_GP?
order by t2.SCORE_GP?
?
[二]、列轉行
?
1.1、初始測試數據
??????? 表結構：TEST_TB_GRADE2
Sql代碼?
create table TEST_TB_GRADE2?
(?
? ID???????? NUMBER(10) not null,?
? USER_NAME? VARCHAR2(20 CHAR),?
? CN_SCORE?? FLOAT,?
? MATH_SCORE FLOAT,?
? EN_SCORE?? FLOAT?
)?
?
??????? 初始數據如下圖：

???????
?
1.2、 如果需要實現如下的查詢效果圖：

??????????????????????
?
這就是最常見的列轉行，主要原理是利用SQL里面的union，具體的sql語句如下：
Sql代碼?
select user_name, '語文' COURSE , CN_SCORE as SCORE from test_tb_grade2??
union select user_name, '數學' COURSE, MATH_SCORE as SCORE from test_tb_grade2??
union select user_name, '英語' COURSE, EN_SCORE as SCORE from test_tb_grade2??
order by user_name,COURSE??
?
?也可以利用【 insert all into ... select 】來實現，首先需要先建一個表TEST_TB_GRADE3：
Sql代碼?
create table TEST_TB_GRADE3???
??? (??
????? USER_NAME VARCHAR2(20 CHAR),???
????? COURSE??? VARCHAR2(20 CHAR),???
????? SCORE???? FLOAT???
??? )???
?再執行下面的sql：
?
Sql代碼?
insert all?
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '語文', CN_SCORE)?
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '數學', MATH_SCORE)?
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英語', EN_SCORE)?
select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2;?
commit;?
?別忘記commit操作，然后再查詢TEST_TB_GRADE3，發現表中的數據就是列轉成行了。

?