Home » SQL & PL/SQL » SQL & PL/SQL » SQL help on hierarchy data (Oracle 10g and DB2)
SQL help on hierarchy data [message #666233] |
Sun, 22 October 2017 13:44 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Hi,
I need your help in getting flatten-hierarchical data for below without using PIVOT
CREATE TABLE hierarchy_data
(
T_EMPL_FIRST VARCHAR2(100), T_EMPL_LAST VARCHAR2(100), I_EMPL VARCHAR2(100), I_EMPL_POSN_ID VARCHAR2(100),
I_DIR_MGR_POSN_ID VARCHAR2(100), I_NXT_MGR_POSN_ID VARCHAR2(100),
I_DIR_MGR_EMPL_ID VARCHAR2(100), I_NXT_MGR_EMPL_ID VARCHAR2(100), EMPLOYEE_LEVEL VARCHAR2(100)
);
INSERT iNTO hierarchy_data values ('Mike','Dell','86721','50087190','','','','','0');
INSERT iNTO hierarchy_data values ('Raman','Sapra','960280','50629600','50087190','50087190','86721','8','1');
INSERT iNTO hierarchy_data values ('Aravind','I','970756','50693135','50087190','50087190','86721','8','1');
INSERT iNTO hierarchy_data values ('Raghu','Jha','964036','50626913','50693135','50693135','970756','97','2');
INSERT iNTO hierarchy_data values ('Rajesh','Moore','897022','50193758','50693135','50693135','970756','97','2');
INSERT iNTO hierarchy_data values ('Shobhit','D','965834','50659457','50626913','50626913','964036','96','3');
INSERT iNTO hierarchy_data values ('Mahesh','P','965788','50659459','50626913','50626913','964036','96','3');
INSERT iNTO hierarchy_data values ('Suresh','K','974714','50162001','50659098','50659457','','96','4');
INSERT iNTO hierarchy_data values ('Ramesh','R','9821','50182348','50659098','50659457','','96','4');
INSERT iNTO hierarchy_data values ('Thiru','S','976941','50587565','50162001','50162001','974714','97','5');
INSERT iNTO hierarchy_data values ('Srini','K','972061','50653532','50162001','50162001','974714','97','5');
commit;
I need output as below, the maximum level is 8
below copied from excel excepted results please bear with the format
FIRST1 NAME1 I_DIR_MGR_EMPL_ID 1 FIRST2 NAME2 I_DIR_MGR_EMPL_ID 2 FIRST3 NAME3 I_DIR_MGR_EMPL_ID 3 FIRST4 NAME4 I_DIR_MGR_EMPL_ID4 FIRST5 NAME5 I_DIR_MGR_EMPL_ID5 FIRST6 NAME6 I_DIR_MGR_EMPL_ID6 EMP LEVEL
Mike Dell NULL Aravind I 86721 Raghu Jha 970756 Shobhit D 964036 Suresh K NULL Thiru S 974714 5
Mike Dell NULL Aravind I 86721 Raghu Jha 970756 Shobhit D 964036 Suresh K NULL Srini K 974714 5
Mike Dell NULL Aravind I 86721 Raghu Jha 970756 Shobhit D 964036 Suresh K NULL NULL NULL NULL 4
Mike Dell NULL Aravind I 86721 Raghu Jha 970756 Shobhit D 964036 Ramesh R NULL NULL NULL NULL 4
Mike Dell NULL Aravind I 86721 Raghu Jha 970756 Shobhit D 964036 NULL NULL NULL NULL NULL NULL 3
Mike Dell NULL Aravind I 86721 Raghu Jha 970756 Mahesh P 964036 NULL NULL NULL NULL NULL NULL 3
Mike Dell NULL Aravind I 86721 Raghu Jha 970756 NULL NULL NULL NULL NULL NULL NULL NULL NULL 2
Mike Dell NULL Aravind I 86721 Rajesh Moore 970756 NULL NULL NULL NULL NULL NULL NULL NULL NULL 2
Mike Dell NULL Aravind I 86721 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1
Mike Dell NULL Raman Sapra 86721 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1
Mike Dell NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0
I tried below but i get null values, i don't want to use any PIVOT. I need it to be done with CASE,DECODE or any other function. As i need this query to run in oracle and DB2. We dont have PIVOT in DB2
SELECT
max( DECODE( EMPLOYEE_LEVEL, 0, T_EMPL_FIRST, NULL ) ) AS first1,
max( DECODE( EMPLOYEE_LEVEL, 1, T_EMPL_FIRST, NULL ) ) AS first2,
max ( DECODE( EMPLOYEE_LEVEL, 2, T_EMPL_FIRST, NULL ) ) AS first3,
max( DECODE( EMPLOYEE_LEVEL, 3, T_EMPL_FIRST, NULL ) ) AS first4,
max ( DECODE( EMPLOYEE_LEVEL, 4, T_EMPL_FIRST, NULL ) ) AS first5,
max ( DECODE( EMPLOYEE_LEVEL, 5, T_EMPL_FIRST, NULL ) ) AS first6,
max ( DECODE( EMPLOYEE_LEVEL, 6, T_EMPL_FIRST, NULL ) ) AS first7,
max ( DECODE( EMPLOYEE_LEVEL,7, T_EMPL_FIRST, NULL ) ) AS first8
FROM hierarchy_data
GROUP BY T_EMPL_FIRST;
|
|
|
Re: SQL help on hierarchy data [message #666255 is a reply to message #666233] |
Mon, 23 October 2017 09:50 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
I've simplified it for the sake of brevity.
CREATE TABLE EMPS (EMPLOYEE_ID NUMBER PRIMARY KEY, MANAGER_ID NUMBER, LAST_NAME VARCHAR2(80), FIRST_NAME VARCHAR2(80));
INSERT INTO EMPS VALUES (1, NULL, 'DELL', 'MIKE');
INSERT INTO EMPS VALUES (2, 1, 'SMITH', 'JOHN');
INSERT INTO EMPS VALUES (3, 1, 'DOE', 'JANE');
INSERT INTO EMPS VALUES (4, 3, 'RODGERS', 'MARK');
ALTER TABLE EMPS ADD FOREIGN KEY (MANAGER_ID) REFERENCES EMPS (EMPLOYEE_ID);
COLUMN FIRST_NAME FORMAT A15
COLUMN LAST_NAME FORMAT A15
SELECT E1.LAST_NAME, E1.FIRST_NAME, E2.LAST_NAME, E2.FIRST_NAME, E3.LAST_NAME, E3.FIRST_NAME
FROM EMPS E1, EMPS E2, EMPS E3
WHERE E1.EMPLOYEE_ID = 1 AND
E2.MANAGER_ID(+) = E1.EMPLOYEE_ID AND
E3.MANAGER_ID (+) = E2.EMPLOYEE_ID;
LAST_NAME FIRST_NAME LAST_NAME FIRST_NAME LAST_NAME FIRST_NAME
------------ ------------ ------------ ------------ ------------ ------------
DELL MIKE DOE JANE RODGERS MARK
DELL MIKE SMITH JOHN
JP
[Updated on: Mon, 23 October 2017 09:51] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:44:08 CDT 2024
|