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,