Hello again,
Sorry to bother you again with my questions, but I was wondering if anyone can advise on this issue. I have an organisational tree and I would like to "flatten" it. Imagine the following sample data:
DROP TABLE dpts;
CREATE TABLE dpts
(
dpt_id NUMBER(10),
dpt_parent_id NUMBER(10),
dpt_name VARCHAR2(100)
);
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(1, NULL, 'research');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(20, 1, 'research.001');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(18, 20, 'research.001.a');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(19, 20, 'research.001.b');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(27, 19, 'research.001.b.a');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(270, 27, 'research.001.b.a.xyz');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(2, NULL, 'hr');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(32, 2, 'hr.eur');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(33, 32, 'hr.eur.ger');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(86, NULL, 'foo');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(77, NULL, 'IT');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(778, 77, 'Helpdesk');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(76, 778, 'Africa');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(779, 77, 'Development');
INSERT INTO dpts(dpt_id, dpt_parent_id, dpt_name) VALUES(799, 77, 'Testing');
COMMIT;
Would it be possible to have this output?
DPT_ID | DPT_NAME | ROOT_NODE | FIRST_LEVEL | SECOND_LEVEL | THIRD_LEVEL
1 | research | research | | |
20 | research.001 | research | | |
18 | research.001.a | research | research.001 | |
19 | research.001.b | research | research.001 | |
27 | research.001.b.a | research | research.001 | research.001.b |
270 | research.001.b.a.xyz | research | research.001 | research.001.b | research.001.b.a
I created output only for some of the records. But basically, I would like to have on the same records, the different parent level names. I will never have more than 4 levels. And to be honest with you, I don't even know how to start with this one. I can display the organisational tree. But, I have no idea how I can proceed to have all the parent nodes on the same record
Thank you again for your help [I'm using ORacle 11g]