Oracle行列轉換的思考與總結__Oracle

最近幾天一直在弄Oracle-SQL的問題，涉及到了一些平時沒有用到的東西，也因此而在這裡鬱悶了好久。現在問題得到瞭解決雖說不算完美。但是還是和大家一起分享一下。

行列轉換之一：sum（case when.. then.. else.. end） as 語句

這種也可能是我們遇到的第一個行列轉換的方法。巧妙的利用匯總和判斷語句就可以解決的。

先看一個簡單的基礎資料表：如下圖

create table STUDENT
(
  STUNAME      NVARCHAR2(50),
  SUBJECTNAME  NVARCHAR2(50),
  SUBJECTSCORE NUMBER
)

表中有兩個人的成績，每一行代表每個學生該學科的成績。這個也是我們初學SQL時候很常見的一個表結構了。

要求：把上面的多行值以學生為單位轉化為2行值，可以很直觀的看出每個學生的各科成績。於是乎便有了下面的語句。

select 
s.stuname,
sum(case when s.subjectname='語文' then  s.subjectscore else 0 end  )  as 語文成績,
sum(case when s.subjectname='數學' then  s.subjectscore else 0 end  )  as 數學成績,
sum(case when s.subjectname='英語' then  s.subjectscore else 0 end  )  as 英語成績
from student s
group by s.stuname
order by s.stuname

執行後所得結果：

上面的做法也很容易讓人理解，在這裡就不多說了，但是上面的也是有局限性的。因為此處畢竟存在著一些固定值。比如case  中的“語文，數學。。”不過遇到小的業務需求，還是可以拿來直接用的。比如一個學產生績管理系統這個就可以滿足了。

行列轉換之二：sys_connect_by_path系統函數，自訂函數，connect by sname = prior sname and rank-1 = prior rank遞迴 語句

素材還是上面的表，下面我想得到的結果是：

把一個對象的各個學科的屬性串連起來放入到一個列中，用字串存放。

當時看到這個業務需求的時候，當然學生這個只是舉個例子。很沒有頭緒。但是業務那邊也急著要資料，沒辦法。只好硬著頭皮想辦法。首先就想起了這個要用連接字串的函數，還需要判斷迴圈之類的。於是乎就想起了這樣做，建立一個自訂函數。來處理字串不斷的相加。

方法1：自訂函數，迴圈

CREATE OR REPLACE FUNCTION getallsubject(parameter varchar2)--有參數方法，字串
RETURN varchar2
IS
return_str varchar2(4000);--該方法返回一個字串。
BEGIN
FOR rs IN
(
SELECT  s.subjectname||':'||s.subjectscore as allshow
FROM student s
WHERE s.stuname=parameter--當參數一直符合條件 for迴圈插入結果集rs
) LOOP
return_str:=return_str||rs.allshow;--loop所有字串，相加
END LOOP;
RETURN return_str;
END;

執行select s.stuname,getallsubject(s.stuname) from  student s

可見，改方法對每一行值都進行了判斷，產生了多條記錄。然後select distinct s.stuname,getallsubject(s.stuname) from  student s

效果：

得到了想要的結果。

方法2：利用oracle內建的sys_connect_by_path

要說明的是：

所以在使用這個函數之前，我們必須先對來源資料進行處理。第一步簡單的處理一下。

create table stu1 as

select s.stuname sname,s.subjectname||s.subjectscore  sshow from student s

--
select *from stu1

如下所示：

OK下面就可以寫語句了。

select sname as 姓名,allstr 詳細描述 from
(
  select sname,allstr,
  row_number() over(partition by sname order by sname,curr_level desc) ename_path_rank
  from (
         select sname,sshow,rank,level as curr_level,
         ltrim(sys_connect_by_path(sshow,','),',') allstr from --把所有字串相加
         (
           select s1.sname,s1.sshow,row_number() over(partition by s1.sname order by s1.sname,s1.sshow) rank
           from stu1 s1 order by s1.sname,s1.sshow--建立樹關係，name可以作為parentid，rank可以作為childid
         ) connect by sname = prior sname and rank-1 = prior rank
       )
)
where ename_path_rank=1;

執行結果：

也得到了我們想要的結果。當然不用學科之間的間隔符我們可以用replace函數自訂。

比較複雜一點的行列轉換用以上兩個方法都可以實現。但是這兩個方法卻都存在著一些優點和缺點。但是本人建議還是使用下面的方法比較靠譜。

雖然結合上面的各種方法解決了這個問題，但是Oracle中字元類型值不能大於4000位元組這個問題還一直沒解決。我在想有沒有一種資料類型，我可以往裡面想放多少位元組就可以放多少位元組的呢。。歡迎各位高手指導。希望可以幫得上遇到該問題的或者是即將遇到該問題的朋友們。

2012-6-18 16:59:51