请教复杂的行列转换问题

lcc · 发表于 2007-1-31 17:49

我的数据如下：
北京东北京东 2006-12-01 B01
北京东北京东 2006-12-02 B11
北京东杨浦 2006-12-08 D11
杨浦杨浦 2006-12-10 B01
杨浦杨浦 2006-12-11 B11
杨浦北京东 2006-12-15 D11
北京东北京东 2006-12-16 B01
……
要求转换成如下格式：
北京东北京东 2006-12-01 北京东 2006-12-02 杨浦 2006-12-08
杨浦杨浦 2006-12-10 杨浦 2006-12-11 北京东 2006-12-15
……
数据源中的第一列不只是北京东和杨浦，还有其他的地名，在每个地方有一次循环（第四列由B01到B11到D11），转换行列的时候其实是同时转换了两列，并且都不是数字型的，我试过了好多方法都不能实现我的要求，希望能够得到各位大侠的帮助！

hanjs · 发表于 2007-1-31 20:53

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

hanjs · 发表于 2007-1-31 21:28

当在增加记录时，上面的不能满足，改成下面的语句了。

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

Toms_zhang · 发表于 2007-1-31 22:26

lcc · 发表于 2007-2-1 08:21

谢谢，我去试一下

lcc · 发表于 2007-2-1 09:17

如果数据不是这么规则，而是中间缺少一些环节，如缺少
北京东北京东 2006-12-02 B11 这一行而还是按原要求转换该如何呢？

hanjs · 发表于 2007-2-1 10:16

最初由 lcc 发布
[B]如果数据不是这么规则，而是中间缺少一些环节，如缺少
北京东北京东 2006-12-02 B11 这一行而还是按原要求转换该如何呢？ [/B]

我倒，那你还说有一个循环？关注下吧！

hanjs · 发表于 2007-2-4 17:14

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

hanjs · 发表于 2007-2-5 09:31

版主呢？这个问题怎么解决啊？

是不是只能写函数了啊！

独自孤单的雨天 · 发表于 2007-2-6 20:22

晕了！

请教复杂的行列转换问题

浏览过的版块