用DECODE和CASE WHEN將多行單列資料改為單行多列資料（即豎向排列改為橫向排列）

1、幾天前，終於修改了SQL查詢語句，改為適應報表格式。其中最關鍵的就是用DECODE和CASE WHEN方式，只要查詢結果正確，那麼用這兩種方式，基本上想要什麼格式就可以拿到什麼格式。

2、DECODE函式使用：

格式是  DECODE(表欄位名，‘數值1’，‘數值2’，‘數值3’，‘數值4’，‘數值5’)。

具體含義：這個函式，實際作用的是，檢查指定的表欄位，如果欄位值=數值1，那麼就返回數值2，如果欄位值=數值3，那麼返回數值4，如果欄位值不等於數值1也不等於數值3，那麼就返回數值5 。也許前面的表述，說的不那麼清楚，就請看下面的示例：

示例表student_arhievement：

student_name	student_arhievement
black	80
jhon	40
jack	80
pher	40
clear	80
ruini	70
pull	30
ada	30
boen	50

   select student_arhievement, decode(student_arhievement,'80','pass A','70','pass B','fail') from student_arhievement;

查詢結果應該是：

black	pass A
jhon	fail
jack	pass A
pher	fail
clear	pass A
ruini	pass B
pull	fail
ada	fail
boen	fail

 

這個查詢結果應該是很明顯了，注意哈，這個查詢結果，我是沒有經過測試的，有可能出錯，也有可能是對的。

我把查詢語句改成：

select decode(student_arhievement,'80','pass','70','pass','fail'),count(*) from student_arhievement group by decode(student_arhievement,'80','pass','70','pass','fail') ;

查詢結果應該是：

pass 4

fail   5

這裡，我在畫蛇添足一下，查詢語句改成這樣：

break on dummy;

compute sum label totalcount of count on dummy;

select null dummy,decode(student_arhievement,'80','pass','70','pass','fail'),count(*) count from student_arhievement group by decode(student_arhievement,'80','pass','70','pass','fail') ;

查詢結果是：

             pass 4

             fail    5

total               9

這個可以給出一個彙總行，就是最後的total行，很不錯的東西。

請注意最後的語句，這裡有個技巧，就是那個null dummy，其實就是一個偽列，欄位名為dummy，但是卻沒有值，所以pass行和fail行的前幾個欄位位置上全是空格，因為是NULL，當然就不顯示了。經過實際測試，還要注意，dummy偽列的列寬度，因為沒有值，所以SQLPLUS會將這個列的寬度定義為1個字元位置，結果導致顯示如下：

       pass 4

       fail    5

t              9

就是最後的彙總行，只顯示了一個T，其實就是TOTAL的第一個字元，因為列寬度不夠，所以只顯示T。辦法就是在語句前面增加一個column dummy format a5就可以了。

關於BREAK和COMPUTR，我估計還需要另外一篇文章來專門寫，內容比較多。

3、CASE WHEN函式

    相對來說，DECODE 函式已經很強大了，但是CASE WHEN函式還要強大的多。

DECODE 函式主要作用是，根據給定的值的要求，就是比如如果欄位值是A，那麼函式返回就是1，如果欄位值是B，那麼返回就是2，這個比較適合指定的欄位值，有明顯的少量基數，就是欄位值比較像男女性別這個值，只有男、女或者未知等，但是如果指定的欄位值，沒有這樣的基數，數值全是亂的，從1到100全有，那麼用DECODE就很不方便了，這時候CASE WHEN就會方便很多。

CASE WHEN格式：

CASE WHEN 表示式 THEN 返回值1 ELSE 返回值2 END；

這個函式，有一部分功能是和DECODE函式一樣的。

仔細看看下面兩個：

decode(archivement ，'80’，'pass’，'fail’)

case when arvhivement='80' then 'pass' else 'fail' end

上面這兩句，結果是一樣的。但是CASE WHEN功能更強。

看下面的表studentu_archievement：

student_name	student_arhievement
black	81
jhon	43
jack	87
pher	35
clear	98
ruini	67
pull	49
ada	89
boen	61

這表中所有同學的成績全部不一樣，這時候用DECODE處理就會很麻煩，CASE WHEN很好用。可以這樣寫：

select case when student_archievement>60 then 'pass' else 'fail' end,count(*) from student_archievement

group by case when student_archievement>60 then 'pass' else 'nopass' end ;

查詢結果：

pass 6

fail    3

這樣處理起來就比DECODE要強多了，語句看起來也要簡單的很多。

4、豎排變橫排的作法

其實，這種豎排變橫排的查詢需求，有一部分是業務系統沒有做好。

看示例表 student_archievement：

student_name	student_class	student_archievement
jack	Mathematics	98
jack	Chinese	67
jack	English	82
ada	Mathematics	78
ada	Chinese	89
ada	English	91
jhon	Mathematics	81
jhon	Chinese	85
jhon	English	79

   從某種意義上講，這樣的表結構很不理想，但是實際情況中，有很多這樣的表結構。

現在需要把查詢結果改成一行就是一個人的3門課的成績，比如像這樣：jack 98 67 82。

處理辦法可以這樣寫：

select student_name,

sum(decode(student_class,'Mathematics',student_archievement)) Mathematics,

sum(decode(student_class,'Chinese',student_archievement)) Chinese,

sum(decode(student_class,'English',student_archievement)) English

from student_archievement

group by student_name;

這句話的執行結果就是：

student_name	Mathematics	Chinese	English
jack	98	67	82
ada	78	89	91
jhon	81	85	79

   這樣的查詢結果，看起來就會舒服很多。

因為文章中的示例資料太過簡單了，所以沒有辦法記錄更復雜的計算辦法，這裡就只寫一些相對簡單的，以後再進一步新增。

我這裡只用到了DECODE函式，當然還是可以用CASE WHEN來做，只是CASE WHEN語句相對複雜點，長度較長而已