oracle数据展平的问题

Question

表b中有如下数据：
id      pid       code     name
1 01 a
2 1 0101 a1
3 1 0102 a2
4 2 010101 a11
5 3 010201 a21
6 1 0103 a3是一个有树型结构的数据，每一层的pid就是它的父结点，如果pid为空，就是根结点了，现在想要得到的数据如下：
id     code1   name1   code2  name2   code3  name3
1      01      a       01     a       01     a
2      01      a       0101   a1      0101   a1
3      01      a       0102   a2      0102   a2
4      01      a       0101   a1      010101 a11
5      01      a       0102   a2      010201 a21
6      01      a       0103   a3      0103   a3就是将列上的数据展到行上，高手指教

Answer 1

如果只是三层的话可以写死来做select b1.id, 
       nvl(b4.code, nvl(b3.code, nvl(b2.code, b1.code))) code1, 
       nvl(b4.name, nvl(b3.name, nvl(b2.name, b1.name))) name1, 
       nvl(b3.code, nvl(b2.code, b1.code)) code2,        
       nvl(b3.name, nvl(b2.name, b1.name)) name2,        
       nvl(b2.code, b1.code) code3, 
       nvl(b2.name, b1.name) name3,        
       from b b1, b b2, b b3, b b4 
        where b1.pid=b2.pid(+) and b2.pid=b3.pid(+) and b3.pid=b4.pid(+)

Answer 2

对楼主的问题挺感兴趣,就试着研究了一下SQL> select * from test01 order by id;
 
        ID        PID CODE       NAME
---------- ---------- ---------- ----------
         1            01         a
         2          1 0101       a1
         3          1 0102       a2
         4          2 010101     a11
         5          3 010201     a21
         6          1 0103       a3
         7          5 01020101   a211
         8          7 0102010101 a2111
 
8 rows selected
想写个能动态处理的:
SQL> declare
  2   str1 varchar2(1000);
  3   str2 varchar2(1000);
  4   str3 varchar2(1000);
  5   str4 varchar2(1000);
  6   str5 varchar2(1000);
  7   str_all varchar2(10000);
  8   colu number;
  9   t number;
 10  begin
 11   select count(*) into t from user_tables where table_name='TEST02';
 12   if t = 1
 13   then execute immediate 'drop table test02';
 14   end if;
 15   select max(level) into colu from test01 start with id = 1 connect by prior id = pid;
 16   str1 := '0';
 17   str2 := '0';
 18   str3 := 'select a1.id as id';
 19   str4 := ' from ';
 20   str5 := ' where (a1.pid = a2.id(+))';
 21   str_all := ' start with a1.id = 1 connect by prior a1.id = a1.pid order by a1.id';
 22   for i in 1..colu loop
 23   str1 := 'nvl(a'||i||'.code,'||str1||')';
 24   str2 := 'nvl(a'||i||'.name,'||str2||')';
 25   str3 := str3||','||str1||' as code'||i||','||str2||' as name'||i;
 26   if i = colu
 27   then
 28   str4 := str4||'test01 a'||i;
 29   else str4 := str4||'test01 a'||i||',';
 30   end if;
 31   if i > 2
 32   then
 33   str5 := str5||' and (a'||(i-1)||'.pid = a'||i||'.id(+))';
 34   end if;
 35   end loop;
 36   str_all := 'create table test02 as '||str3||str4||str5||str_all;
 37   execute immediate str_all;
 38  end;
 39  /
 
PL/SQL procedure successfully completed
看看结果:
SQL> select * from test02;
 
        ID CODE1      NAME1      CODE2      NAME2      CODE3      NAME3      CODE4      NAME4      CODE5      NAME5
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 01         a          01         a          01         a          01         a          01         a
         2 0101       a1         01         a          01         a          01         a          01         a
         3 0102       a2         01         a          01         a          01         a          01         a
         4 010101     a11        0101       a1         01         a          01         a          01         a
         5 010201     a21        0102       a2         01         a          01         a          01         a
         6 0103       a3         01         a          01         a          01         a          01         a
         7 01020101   a211       010201     a21        0102       a2         01         a          01         a
         8 0102010101 a2111      01020101   a211       010201     a21        0102       a2         01         a
 
8 rows selected
删了一行:
SQL> delete from test01 where id = 8;
 
1 row deleted
 
SQL> select * from test01 order by id;
 
        ID        PID CODE       NAME
---------- ---------- ---------- ----------
         1            01         a
         2          1 0101       a1
         3          1 0102       a2
         4          2 010101     a11
         5          3 010201     a21
         6          1 0103       a3
         7          5 01020101   a211
 
7 rows selected
 
SQL> commit;
 
Commit complete
再试一下:
SQL> declare
  2   str1 varchar2(1000);
  3   str2 varchar2(1000);
  4   str3 varchar2(1000);
  5   str4 varchar2(1000);
  6   str5 varchar2(1000);
  7   str_all varchar2(10000);
  8   colu number;
  9   t number;
 10  begin
 11   select count(*) into t from user_tables where table_name='TEST02';
 12   if t = 1
 13   then execute immediate 'drop table test02';
 14   end if;
 15   select max(level) into colu from test01 start with id = 1 connect by prior id = pid;
 16   str1 := '0';
 17   str2 := '0';
 18   str3 := 'select a1.id as id';
 19   str4 := ' from ';
 20   str5 := ' where (a1.pid = a2.id(+))';
 21   str_all := ' start with a1.id = 1 connect by prior a1.id = a1.pid order by a1.id';
 22   for i in 1..colu loop
 23   str1 := 'nvl(a'||i||'.code,'||str1||')';
 24   str2 := 'nvl(a'||i||'.name,'||str2||')';
 25   str3 := str3||','||str1||' as code'||i||','||str2||' as name'||i;
 26   if i = colu
 27   then
 28   str4 := str4||'test01 a'||i;
 29   else str4 := str4||'test01 a'||i||',';
 30   end if;
 31   if i > 2
 32   then
 33   str5 := str5||' and (a'||(i-1)||'.pid = a'||i||'.id(+))';
 34   end if;
 35   end loop;
 36   str_all := 'create table test02 as '||str3||str4||str5||str_all;
 37   execute immediate str_all;
 38  end;
 39  /
 
PL/SQL procedure successfully completed
 
SQL> select * from test02;
 
        ID CODE1      NAME1      CODE2      NAME2      CODE3      NAME3      CODE4      NAME4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 01         a          01         a          01         a          01         a
         2 0101       a1         01         a          01         a          01         a
         3 0102       a2         01         a          01         a          01         a
         4 010101     a11        0101       a1         01         a          01         a
         5 010201     a21        0102       a2         01         a          01         a
         6 0103       a3         01         a          01         a          01         a
         7 01020101   a211       010201     a21        0102       a2         01         a
 
7 rows selected
总是向后挪一列......
谁再改一下估计就可以了,要下班了,楼主自己改一下吧