複雜的行列轉換

SQL> create table test_7 (addr_from varchar2(20),addr_to varchar2(20),start_date date,no varchar2(10));

Table created

SQL> insert into test_7 values ('北京東','北京東',to_date('2006-12-01','yyyy-mm-dd'),'B01');

1 row inserted

SQL> insert into test_7 values ('北京東','北京東',to_date('2006-12-02','yyyy-mm-dd'),'B11');

1 row inserted

SQL> insert into test_7 values ('北京東','楊浦',to_date('2006-12-08','yyyy-mm-dd'),'D11');

1 row inserted

SQL> insert into test_7 values ('楊浦','楊浦',to_date('2006-12-10','yyyy-mm-dd'),'B01');

1 row inserted

SQL> insert into test_7 values ('楊浦','楊浦',to_date('2006-12-11','yyyy-mm-dd'),'B11');

1 row inserted

SQL> insert into test_7 values ('楊浦','北京東',to_date('2006-12-15','yyyy-mm-dd'),'D11');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test_7;

ADDR_FROM ADDR_TO START_DATE NO
-------------------- -------------------- ----------- ----------
北京東 北京東 2006-12-1 B01
北京東 北京東 2006-12-2 B11
北京東 楊浦 2006-12-8 D11
楊浦 楊浦 2006-12-10 B01
楊浦 楊浦 2006-12-11 B11
楊浦 北京東 2006-12-15 D11

6 rows selected

SQL> select addr_from||replace(max(sys_connect_by_path(addr_to,',')),',',' ') path from (
2
2 select addr_from,addr_from||(rn - no) as addr,addr_to,rn,no
3 from (
4 select addr_from,addr_to||' '||to_char(start_date,'yyyy-mm-dd') as addr_to,
5 row_number() over (order by 1) as rn,decode(no,'B01',1,'B11',2,'D11',3) as no
6 from test_7)
7 )
8 start with no=1
9 connect by rn-1 = prior rn
10 group by addr_from,addr;

PATH
--------------------------------------------------------------------------------
楊浦 楊浦 2006-12-10 楊浦 2006-12-11 北京東 2006-12-15
北京東 北京東 2006-12-01 北京東 2006-12-02 楊浦 2006-12-08

[@more@]

當在增加記錄時，上面的不能滿足，改成下面的語句了。

SQL> insert into test_7 values ('北京東','北京東',to_date('2006-12-16','yyyy-mm-dd'),'B01');

1 row inserted

SQL> insert into test_7 values ('北京東','北京東',to_date('2006-12-17','yyyy-mm-dd'),'B11');

1 row inserted

SQL> insert into test_7 values ('北京東','楊浦',to_date('2006-12-23','yyyy-mm-dd'),'D11');

1 row inserted

SQL> commit;

Commit complete

Select addr_from||replace(max(sys_connect_by_path(addr_to,',')),',',' ') path from (
select addr_from,addr_from||(n - no) as addr,addr_to,rn,No
from (
select addr_from,addr_to||' '||to_char(start_date,'yyyy-mm-dd') as addr_to,
row_number() over (order by 1) as n,
2*row_number() over (order by 1) - decode(no,'B01',1,'B11',2,'D11',3) as rn,decode(no,'B01',1,'B11',2,'D11',3) as no
from test_7)
)
start with no=1
connect by rn-1 = prior rn
group By addr,addr_from;


楊浦 楊浦 2006-12-10 楊浦 2006-12-11 北京東 2006-12-15
北京東 北京東 2006-12-01 北京東 2006-12-02 楊浦 2006-12-08
北京東 北京東 2006-12-16 北京東 2006-12-17 楊浦 2006-12-23

如果資料不是這麼規則，而是中間缺少一些環節，如缺少
北京東 北京東 2006-12-02 B11 這一行而還是按原要求轉換該如何呢？

SQL> select * from test_7 order by start_date;

ADDR_FROM ADDR_TO START_DATE NO
-------------------- -------------------- ----------- ----------
北京東 北京東 2006-12-1 B01
北京東 楊浦 2006-12-8 D11
楊浦 楊浦 2006-12-10 B01
楊浦 楊浦 2006-12-11 B11
楊浦 北京東 2006-12-15 D11
北京東 北京東 2006-12-16 B01
北京東 北京東 2006-12-17 B11
北京東 楊浦 2006-12-23 D11
楊浦 楊浦 2006-12-25 B01
楊浦 楊浦 2006-12-26 B11
楊浦 北京東 2006-12-29 D11

11 rows selected

SQL> select path from (
2 Select addr_from||replace(sys_connect_by_path(addr_to,','),',',' ') path,rn,n from (
3 select addr_from,addr_to||' '||to_char(start_date,'yyyy-mm-dd') as addr_to,
4 row_number() over (order by start_date) as rn,
5 dense_rank() over (partition by addr_from order by no) as n
6 from test_7
7 order by start_date)
8 start with n=1
9 connect by rn-1 = prior rn and addr_from = prior addr_from
10 ) where n=3
11 /

PATH
--------------------------------------------------------------------------------
北京東 北京東 2006-12-01 楊浦 2006-12-08
楊浦 楊浦 2006-12-10 楊浦 2006-12-11 北京東 2006-12-15
北京東 北京東 2006-12-16 北京東 2006-12-17 楊浦 2006-12-23
楊浦 楊浦 2006-12-25 楊浦 2006-12-26 北京東 2006-12-29

注意，可以缺少B11，但是不能缺少D11

轉自 http://www.itpub.net/716770.html