關于豎表轉橫表的問題
本文作者：dinya
內容摘要：在開發過程，經常遇到一些將表的顯示方式進行轉換的需求，我們習慣性稱之為豎表到橫表的轉換，本文通過一個例子來簡要說明常見的兩種豎表轉橫表的問題。
本文適宜讀者范圍：oracle初級，中級
系統環境：
os：windows 2000 professional (英文版)
oracle：8.1.7.1.0
正文：
在實際的應用中，我們經常遇到需要轉換數據顯示方式，比如將橫表轉為豎表，或將豎表轉換為橫表的情況，如：課程表的顯示方式，部門平均工資的排名等情況。下面將將根據兩個實例子的需求描述給出兩種常見的豎表轉橫表的解決辦法（本例中的數據意思是：一、二、三年級的各科目最高分統計）。
表結構：
create table test_table
(
grade_id number(8), --年級：１、一年級，２、二年級，３、三年級
subject_name varchar2(30), --科目：包含語文、數學、外語、政治等科目
max_score number(8) --最高分
)
表中數據：
sql> select * from test_table;
grade_id subject_name max_score
1 語文 95
1 數學 98
2 語文 86
2 數學 90
2 政治 87
3 語文 93
3 數學 88
3 英語 88
3 政治 97
9 rows selected.
第一種轉換方式：
需求描述：查看每個年級在系統中存在的科目信息，并各年級的科目信息按下面的格式顯示：
grade_id subject_name
1 語文 數學
2 語文 數學 政治
3 語文 數學 英語 政治
分析：在要求得到的結果中，每個年級的科目將變成一條記錄，而且每個年級的科目是不固定的。所以考慮寫個函數來解決，輸入年級信息，使用游標得到該年級的所有科目信息并返回值。
1、建函數：
sql> create or replace function test_fun(p_grade number) return varchar2 as
2 v_temp varchar2(100):='';
3 v_out varchar2(500):='';
4 cursor c is select a.subject_name from test_table a where a.grade_id=p_grade;
5 begin
6 open c ; --打開游標
7 loop
8 fetch c into v_temp;
9 exit when c%notfound;
10 v_out:=v_out||' '||v_temp;
11 end loop;
12 close c; --關閉游標
13 return v_out;
14 exception
15 when others then
16 return 'an error occured';
17 end ;
18 /
function created.
sql> create or replace function test_fun(p_grade number) return varchar2 as
2 v_out varchar2(500):='';
3 cursor c is select a.subject_name from test_table a where a.grade_id=p_grade;
4 begin
5 for v_temp in c loop
6 v_out:=v_out||' '||v_temp.subject_name;
7 end loop; --系統自動關閉游標
8 return v_out;
9 exception
10 when others then
11 return 'an error occured';
12 end ;
13 /
function created.
2、調用函數得到輸入結果：
sql> select distinct a.grade_id,test_fun(a.grade_id) subject from test_table a；
grade_id subject
1 語文 數學
2 語文 數學 政治
3 語文 數學 英語 政治
第二種轉換方式：
需求描述：要求將表中的年級、科目及最高的信息按照下表的格式顯示,如果該年級沒開的課程,則其最高分用0表示:
年級 語文 數學 英語 政治
一年級 95 98 0 0
二年級 86 90 0 87
三年級 93 88 88 97
分析：該需求將年級的分數及科目信息由縱向轉為橫向，這樣就要針對每個年級的，對其科目進行判斷，存在科目則顯示科目的最高分，如果不存在顯示0。這時候就考慮到使用decode函數來解決。實現如下：
select
decode(t.grade_id,1,'一年級',2,'二年級',3,'三年級') 年級,
sum(decode(t.subject_name,’語文’,t.max_score,0)) 語文,
sum(decode(t.subject_name,'數學',t.max_score,0)) 數學,
sum(decode(t.subject_name,'英語',t.max_score,0)) 英語,
sum(decode(t.subject_name,'政治',t.max_score,0)) 政治
from
test_table t
group by
t.grade_id
需要說明的是，在第一種轉換方式中寫了兩個函數,兩個函數實現的是同一個需求，所不同的是，兩個函數中游標使用方式不同，地一個函數中手動打開游標，循環結束后要求手動關閉。而后一個函數使用for 循環，循環結束后系統自動關閉光標。在第二種轉換方式中，使用了decode函數，關于decode的詳細用法，請參考oracle函數相關文檔。
總 結：
上面的兩種轉換方式是在開發中經常遇到的情況,在開發中的其他類似的轉換都可以參考上面的轉換方式,使用decode,nvl等函數進行一些特別的處理即可得到想要的顯示方式.
本文你可以在作者的blog上找到，更多內容請登陸作者的blog。
作者blog: http://blog.csdn.net/dinya2003/
如轉載,請保留作者blog信息.
