Re: How to put hierarchy into columns

<jaspas_at_poczta.onet.pl> a écrit dans le message de news: 1132765677.690908.38070_at_z14g2000cwz.googlegroups.com...
| Hello,
|
| I have table:
|
| create table corporate_slaves (
| slave_id integer primary key,
| supervisor_id references corporate_slaves,
| name varchar(100)
| );
|
| insert into corporate_slaves values (1, NULL, 'Big Boss');
| insert into corporate_slaves values (2, 1, 'VP Mark');
| insert into corporate_slaves values (3, 1, 'VP Sal');
| insert into corporate_slaves values (4, 3, 'Joe');
| insert into corporate_slaves values (5, 4, 'Bill');
| insert into corporate_slaves values (6, 1, 'VP Engi');
| insert into corporate_slaves values (7, 6, 'Jane');
| insert into corporate_slaves values (8, 6, 'Bob');
|
|
| How I can put hierarchy into columns to get result like this:
|
| Parent Parent-1 Parent-2 Parent-3 Parent -4
| Lowest
| -------------- ------------ -------------- ------------
| -------------- ------------
| Big Boss
| Big Boss
| Big Boss VP Mark
| VP Mark
| Big Boss Vp Sal
| VP Sal
| Big Boss VP Sal Joe
| Joe
| Big Boss VP Sal Joe Bill
| Bil
| Big Boss VP Engi
| Engi
| Big Boss VP Engi Jane
| Jane
|
|
| I used simply "connect by" but it's not what I need
|
| SELECT LPAD(' ',(LEVEL-1)*4,' ')||name
| FROM corporate_slaves
| CONNECT BY PRIOR slave_id=supervisor_id
|
| Can you help me?
| Thank you in advance,
| Jaspis
|

Have a look at sys_connect_by_path function: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions121a.htm#88362

Regards
Michel Cadot Received on Wed Nov 23 2005 - 12:39:24 CST