關于豎表轉橫表的問題 本文作者：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 政治 979 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 SUBJECT1 語文 數學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函數相關文檔?？偂 〗Y：上面的兩種轉換方式是在開發中經常碰到的情況,在開發中的其他類似的轉換都可以參考上面的轉換方式,使用decode,nvl等函數進行一些非凡的處理即可得到想要的顯示方式.本文你可以在作者的Blog上找到，更多內容請登陸作者的Blog。
作者Blog: http://blog.csdn.net/dinya2003/
如轉載,請保留作者Blog信息.
