Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Hierarchical query - CONNECT BY and result in different columns.

user13117585Mar 1 2011 — edited Mar 1 2011
Hi everyone,

I have a small question, hoping that someone can help me. As you will see, this
is not very important. It's just me wondering if I can learn something in SQL.

Let's suppose I have an ORGANIZATIONS table:
CREATE TABLE ORGANIZATIONS (
  ID_ORGANISATION NUMBER(10) PRIMARY KEY, 
  NAME VARCHAR2(255) NOT NULL, 
  ID_PARENT_ORGANIZATION NUMBER(10)
);

INSERT INTO ORGANIZATIONS(1, 'Parent of all', null);
INSERT INTO ORGANIZATIONS(11, 'Child 11', 1);
INSERT INTO ORGANIZATIONS(111, 'Child 111', 11);
INSERT INTO ORGANIZATIONS(112, 'Child 112', 12);
INSERT INTO ORGANIZATIONS(12, 'Child 12', 1);
INSERT INTO ORGANIZATIONS(121, 'Child 121', 12);
Let's also assume that we can have an undefined number of levels.

Now, my question:
With a query like
SELECT LPAD(' ', LEVEL) || NAME  as conc
  FROM ORGANIZATIONS 
 START WITH ID_ORGANIZATION = 1
CONNECT BY PRIOR ID_ORGANISATION = ID_PARENT_ORGANIZATION
..
I have a result like
 [conc]
 "Parent of all" 
   "Child 11" 
     "Child 111"
     "Child 112"
   "Child 12" 
   ...
All in one columns.

What I'd like is a result like this one:
[LEVEL_1]         [LEVEL_2]      [LEVEL_3] 
"Parent of all" 
                  "Child 11" 
                                 "Child 111"
                                 "Child 112"
                  "Child 12" 
                                 "Child 121"
I'd like to have this structure for the 5 first levels. Do you think this is possible or not?

Thanks for your advice,
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 29 2011
Added on Mar 1 2011
2 comments
192 views