用sql語句實現行變列

在實際應用中，經常會碰到要求將行轉換成列的形式來顯示查詢結果。

現舉例說明，
有如圖A所示的表stat,表的內容為各個班級擁有水果的數量，要求將表stat的查詢結果以圖B的形式顯示。

class        fruit     amount
-----------------------------
class a      apple     30
class a      pear      15
class b      apple     40
class b      pear      20
  圖 A

class    apple_amount pear_amount
----------------------------
class a      30        15
class b      40        20
  圖 B
 
實現SQL如下： 
 select class, sum(apple_amount) apple_amount, sum(pear_amount) pear_amount
  from (
    select class, decode(fruit, ''apple'', amount, 0) apple_amount, decode(fruit, ''pear'', amount, 0) pear_amount
    from stat
  )
  group by class
 
建表腳本：
 create table stat(class varchar2(30), fruit varchar2(30), amount number(10));
 insert into stat(class, fruit, amount)
 values(''class a'', ''apple'', 30);
 insert into stat(class, fruit, amount)
 values(''class a'', ''pear'', 15);
 insert into stat(class, fruit, amount)
 values(''class b'', ''apple'', 40);
 insert into stat(class, fruit, amount)
 values(''class b'', ''pear'', 20);
 commit;