Re: How to put hierarchy into columns

From: <mikharakiri_nospaum_at_yahoo.com>
Date: 23 Nov 2005 14:15:38 -0800
Message-ID: <1132784138.087412.171780@z14g2000cwz.googlegroups.com>

jaspas_at_poczta.onet.pl wrote:
> 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

A problem with hierarcky doesn't necessarily imply the need in hierarchical query

select ... from

corporate_slaves cs_level1,
corporate_slaves cs_level2,
corporate_slaves cs_level3,
corporate_slaves cs_level4,

where cs_level1.id= cs_level2.parent_id(+) and cs_level2.id = cs_level3.parent_id(+) and cs_level3.id = cs_level4.parent_id(+)

P.S. Appreciate your sence of humor Received on Wed Nov 23 2005 - 16:15:38 CST