開發商業智慧系統時, Crosstab的分析是很重要的技術, 或稱為Pivot. 將資料轉置成欄和列都有各自代表的維度, 而相對應的值就放置在中間, 如下表所示:
外銷總數量
USA China Japan
Jan 100 150 100
Feb 50 20 10
Mar 20 40 50
Apr 10 50 60
...
這對Excel而言是一件簡單的事情，因為樞紐分析大家都會，但是對關聯式資料庫而言，是一個
很大的挑戰， 因為關聯式資料庫的資料擺放型態是 Column-Value, 如下表
:
月份 外銷國 數量
Jan USA 10
Feb China 30
....................................
這時在 Oracle怎麼解決呢? 有些人會在呈現端-Client想辦法, 不管是VB, ASP,
JSP將資料從Oracle讀進ResultSet後，聰明的程式設計師總有辦法將資料作CrossTab
的處理, 但問題是通常程式寫完後如果換做是其他人接手，應該會看不懂程式碼在寫
什麼？原因無他，因為太複雜！還有一個辦法是買 Solution, 像 Hyperion,
Oracle Discoverer, Business Object等應該都內建一些報表工具可以做CrossTab,
缺點不用說就是貴，不用個幾百萬，案子應該起不來!
如果可以在 Oracle端就將資料處理好, 再搭配前端的資料呈現工具，有一些比較簡單
的作法.常見的像是以下的SQL, 用 Decode 或是 Case, 用兩層SQL兜出想要的結果.但
是缺點呢? 一個是寫法還是有點小複雜, 如再要多個法國的外銷數量, 程式還要改.
當然後者的問題好解決，可將此邏輯寫成一個Function, 將可能的國別傳入Function後,
再透過該Function兜出SQL後執行.
=========================================================================
select Month,China,USA,Japan,China+USA+Japan
from (
select
Month,
max(case when Country='China' then qty else 0 end) China,
max(case when Country='USA' then qty else 0 end) USA,
max(case when Country='Japan' then qty else 0end) Japan
from Sales_Value
group by Month
);
=========================================================================
OTN有一篇文章
(http://kr.forums.oracle.com/forums/thread.jspa?messageID=2487130)
就是談這一段，下面是節錄自該文章的Source Code. 原則上就是將要做
CrossTab的相關資訊傳入後，將SQL組合出來，有些語法會有點看不懂，因為程式裡
兜出來的SQL是用11G最新的Pivot SQL.
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
create or replace function getPivotSql(
p_sql in varchar2,
p_x_col in varchar2,
p_x_col_type in varchar2 default 'DATE',
p_x_col_start in varchar2,
p_x_col_interval in varchar2,
p_x_col_int_unit in varchar2 default 'DAY',
p_x_col_count in number,
p_y_col in varchar2,
p_cell_col in varchar2,
p_cell_col_aggr in varchar2 default NULL
) return varchar2
is
v_sql varchar2(32767);
begin
v_sql := '';
v_sql := v_sql || 'with data as ('||chr(10);
v_sql := v_sql || ' '||p_sql||chr(10);
v_sql := v_sql || ')';
if p_x_col_type = 'VARCHAR2' then
v_sql := v_sql ||', x_dist_values as ('||chr(10);
v_sql := v_sql ||' select distinct '||p_x_col||' val from data order by 1'||chr(10);
v_sql := v_sql ||'), x_values_rownum as ('||chr(10);
v_sql := v_sql ||' select rownum zeile, val from x_dist_values where rownum <= '||p_x_col_count||chr(10); v_sql := v_sql ||')'||chr(10); else v_sql := v_sql || chr(10); end if; v_sql := v_sql || 'select distinct '||chr(10); v_sql := v_sql || ' data.'||p_y_col||','||chr(10); for i in 1..p_x_col_count loop if p_cell_col_aggr is not null then v_sql := v_sql || p_cell_col_aggr||'('; end if; v_sql := v_sql || ' case when '; if p_x_col_type = 'VARCHAR2' then v_sql := v_sql || 'x.zeile = '||i; elsif p_x_col_type = 'NUMBER' then v_sql := v_sql || ' data.'||p_x_col||' between '|| p_x_col_start||' + ('||(i - 1)||' * '|| p_x_col_interval || ') and '||p_x_col_start||' + ('|| i||' * '||p_x_col_interval||') '; elsif p_x_col_type = 'DATE' then v_sql := v_sql || ' data.'||p_x_col||' between '|| p_x_col_start||' + interval '''||((i - 1) * p_x_col_interval )|| ''' '||p_x_col_int_unit||' and '||p_x_col_start|| ' + interval '''||i * p_x_col_interval ||''' '||p_x_col_int_unit; end if; v_sql := v_sql ||' then '||p_cell_col|| ' else null end'; if p_cell_col_aggr is not null then v_sql := v_sql || ')'; end if; v_sql := v_sql || ' as "VALUE"'; if i < p_x_col_type =" 'VARCHAR2'">
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
下面就來細究Pivot SQL的語法 吧！將前面介紹的第一種方式改寫成新版本會向如下!
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
select * from (
select Month, Country
from Sales_Value t
)
pivot
(
count(Country)
for Month in ('USA','China','Japan')
)
order by Month;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
看起來，簡單多了吧！ 結合起第二步驟的Function，CrossTab是不是變成比較簡單有條理
而且彈性變大了？ 不用花錢買BI Solution也可以讓公司使用者享有Excel樞紐分析的功能
喔！
OTN原文如下：
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html
