DanBrother的部落格

當我們需要一份報表以橫式方現呈現時,可以用 SYS_CONNECT_BY_PATH 函式達成:

-- 建立測試資料表
create table test_regist
(input_time date,
 regist number(10)
) pctfree 10 pctused 75;

-- 寫入測試數據
declare
v_now date default to_date('2009-01-01','yyyy-mm-dd');
v_input_time date;
v_regist number(10);
begin
    v_input_time:=v_now;
    for i in 1 ..30 loop
        v_input_time:=v_input_time+12;
        v_regist:=trunc(dbms_random.value(1,999));       
        insert into test_regist(input_time,regist)
        values(v_input_time,v_regist);
    end loop;
commit;
end;
/

-- 列出測試資料:
select * from test_regist;

-- 橫式顯示年度註冊統計
select max(ltrim(SYS_CONNECT_BY_PATH(num||'>'||total_regist,'  '),'  ')) as total_regist
from
(
select rownum as num,month,total_regist
from
(
select to_char(trunc(input_time,'mm'),'yyyy-mm') as month,sum(regist) as total_regist
from test_regist
group by to_char(trunc(input_time,'mm'),'yyyy-mm')
)
)
start with num = 1 connect by prior num+1 = num;

<補充>
以 Decode方式也可達成橫式統計:

select nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-01',regist,0)),0) as "2009-01",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-02',regist,0)),0) as "2009-02",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-03',regist,0)),0) as "2009-03",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-04',regist,0)),0) as "2009-04",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-05',regist,0)),0) as "2009-05",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-06',regist,0)),0) as "2009-06",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-07',regist,0)),0) as "2009-07",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-08',regist,0)),0) as "2009-08",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-09',regist,0)),0) as "2009-09",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-10',regist,0)),0) as "2009-10",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-11',regist,0)),0) as "2009-11",
       nvl(sum(decode(to_char(input_time,'yyyy-mm'),'2009-12',regist,0)),0) as "2009-12"
from test_regist;