CODEWITHSUNDEEP
sqloracle-databasehierarchical-data
RIPI
RIPI

Reputation: 351

Hierarchical query - selecting filtered employees and their leaders in neighbour-colums (not rows)

So I need to present the hierarchy not in rows using f.e. LPAD() but in colums. So for example if I have this relation 

Employees (name VARCHAR(15), function VARCHAR(15), #leader)

and functions are from 1 to 6, how to present employees who have function 5 or 6 with all their leaders? I mean if we present that in rows using LPAD and starting from the lower levels it will look like that:

name 
Mike -->(function 5)
   Bruce
       Lee
Lea  -->(function 6)
   Fred
       Mike
          Bruce

I need it to look like this:

name     function    leader1(lowest level)  leader2   leader3(highest level in this example)
Mike        5              Bruce              Lee
Lea         6              Fred               Mike         Bruce

and of course it should've been filtered that Mike, Lea etc. have function 5 or 6 and it doesn't matter which function have Bruce, Lee, Fred etc.

ok, to make helping me easier I've created example of database with data with query I use to show the result in rows with LPAD

CREATE TABLE Employees (
name VARCHAR2(15) CONSTRAINT name_pk PRIMARY KEY,
function NUMBER(2) CONSTRAINT function_nn NOT NULL,
leader VARCHAR2(15) CONSTRAINT leader_fk REFERENCES Employees(name)
);


alter table
Employees
DISABLE constraint
leader_fk;


INSERT INTO Employees VALUES ('Mike', 5, 'Lee');
INSERT INTO Employees VALUES ('Bruce', 5, 'Lee');
INSERT INTO Employees VALUES ('Lee', 3, NULL);
INSERT INTO Employees VALUES ('Lea', 6, 'Fred');
INSERT INTO Employees VALUES ('Fred', 1, 'Mike');


alter table
Employees
ENABLE constraint
leader_fk;

WITH 
hierarchia (poziom, name, function, leader)
AS (
SELECT 0 poziom, name, function, leader
FROM Employees
WHERE function IN (5, 6) 
UNION ALL 
SELECT
h.poziom + 1, e.name, e.function, e.leader
FROM hierarchia h, Employees e
WHERE h.leader = e.name
)
SEARCH DEPTH FIRST BY name SET order_by_name
SELECT LPAD(' ', 2*poziom) || name, function
FROM hierarchia
ORDER BY order_by_name;

So it returns correct answer but I need to show LPADed names as neighbour-colums as I've shown in example.

Upvotes: 0

Views: 72

Answers (1)

dcieslak
dcieslak

Reputation: 2715

WITH 
hierarchia (root, poziom, name, function, leader)
AS (
SELECT  name, 0 poziom, name, function, leader
FROM Employees
WHERE function IN (5, 6) 
UNION ALL 
SELECT h.root,
h.poziom + 1, e.name, e.function, e.leader
FROM hierarchia h, Employees e
WHERE h.leader = e.name
)
select Root, function, Leader1, Leader2 , Leader3  from (
select root,  poziom, name from hierarchia )
pivot
(
 max(Name) 
 for poziom in (  1 as leader1, 2 as leader2, 3 as leader3) 
)  X
join Employees e
on e.Name = X.root

PIVOT function will change your rows to columns. 

Root    Function    Leader1 Leader2 Leader3
Lea     6           Fred    Mike    Lee
Bruce   5           Lee     
Mike    5           Lee

Upvotes: 1

Related Questions