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

ITPub博客

复杂的行列转换

最新文章