Re: How to put hierarchy into columns

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 24 Nov 2005 01:27:50 -0800
Message-ID: <1132824470.859650.259960@g47g2000cwa.googlegroups.com>

Jaspis is using the sample data from Greenspun. You will find useful code snippets for all sorts of hierarchical queries there.

http://philip.greenspun.com/sql/trees.html

Here is a further implementation for Jaspis, a key point being use of CONNECT_BY_ROOT (10g).

Regards
Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429

SQL>
SQL> COLUMN "Parent Lowest" FORMAT A10
SQL> COLUMN "Parent-1" FORMAT A10
SQL> COLUMN "Parent-2" FORMAT A10
SQL> COLUMN "Parent-3" FORMAT A10
SQL> COLUMN "Parent-4" FORMAT A10
SQL>
SQL> SET TRUNC ON
SQL> SET WRAP OFF
SQL>
SQL> SELECT CONNECT_BY_ROOT name "Parent Lowest",
  2         DECODE(LEVEL,1,name,NULL) "Parent-1",
  3         DECODE(LEVEL,2,name,NULL) "Parent-2",
  4         DECODE(LEVEL,3,name,NULL) "Parent-3",
  5         DECODE(LEVEL,4,name,NULL) "Parent-4"

  6 FROM corporate_slaves
  7 CONNECT BY PRIOR slave_id=supervisor_id   8 START WITH supervisor_id IS NULL;

Parent Low Parent-1 Parent-2 Parent-3 Parent-4 ---------- ---------- ---------- ---------- ---------- Big Boss Big Boss

Big Boss              VP Mark
Big Boss              VP Sal
Big Boss                         Joe
Big Boss                                    Bill
Big Boss              VP Engi
Big Boss                         Jane
Big Boss                         Bob

8 rows selected.

SQL>
SQL> SELECT *
  2 FROM V$VERSION; BANNER

---

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Solaris: Version 10.1.0.3.0 - Production NLSRTL Version 10.1.0.3.0 - Production

SQL>
SQL>
SQL>

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
>
> Can you help me?
> Thank you in advance,
> Jaspis
Received on Thu Nov 24 2005 - 03:27:50 CST