Hierarchy, and then some

Question

Post reply

Hierarchy, and then some

John White-329912

SSC Rookie

Points: 33
More actions
December 31, 2007 at 8:23 am

#181712

I need to produce a file that contains a record for every employee, their department, and the departments for which they have a subordinate employee. So far so good, but I also need records for each department for which their subordinates have subordinates, etc. In my case that goes seven levels deep.
So if the raw data looks like this:
Emp Supervisor Dept.
1 1 1000
2 1 2000
3 2 3000
4 2 4000
5 3 5000
Then I need:
Emp Dept.
1 1000
1 2000
1 3000
1 4000
1 5000
2 2000
2 3000
2 4000
2 4000
3 3000
3 5000
4 4000
5 5000
Another way to look at it is that employee 1 - who is the president of the company - would have a record for every department, since all departments roll up to him.
Thanks for your help.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

A recursive CTE is perfect for this. This may not be 100%, but should give you the idea.

;WITH RecursiveEmployees (Emp, Supervisor, Dept) AS

(

SELECT Emp, Supervisor, Dept FROM Employees WHERE Emp=Supervisor -- root elements

UNION ALL

SELECT Emp, Supervisor, Dept

FROM Employees INNER JOIN RecursiveEmployees -- Join back on the CTE

ON Employees.Supervisor = RecursiveEmployees.Emp -- I think this is the right way round

WHERE Emp!=Supervisor -- Make sure we don't get the root again

)

SELECT Emp, Dept FROM RecursiveEmployees

ORDER BY Emp, Dept

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John White-329912 SSC Rookie Points: 33 More actions · Answer 2

Beautiful, thank you. I won't be able to test this until Wednesday, but I think I can take it from here.

Thanks again!

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

This should help:

DECLARE @tbl TABLE(

Emp INT,

Supervisor INT,

Dept INT

)

--Emp | Supervisor | Dept

INSERT INTO @tbl

SELECT 1, 1, 1000 UNION ALL

SELECT 2, 1, 2000 UNION ALL

SELECT 3, 2, 3000 UNION ALL

SELECT 4, 2, 4000 UNION ALL

SELECT 5, 3, 5000

--get the president

SELECT a.Emp,b.Dept

FROM @tbl a, @tbl b

WHERE a.Emp <= b.Emp AND a.Emp = 1

UNION ALL

--get everyone else

SELECT a.Emp,b.Dept

FROM @tbl a, @tbl b

WHERE a.Emp = b.Emp AND a.Emp > 1

OR a.Emp = b.Supervisor AND a.Emp > 1

ORDER BY a.Emp, b.Dept

My blog: http://jahaines.blogspot.com

John White-329912 SSC Rookie Points: 33 More actions · Answer 4

Doing some validation, but this appears to work perfectly - thanks!

Gail - I want to try your method also, but I'm actually working in Oracle. I hit a dead end, so I thought I'd take a look at doing this in SQL Server using a linked Oracle server. Oracle has some functions that handle this nicely, but they don't appear until version 10, and I'm using 9.

Adam's code runs in Oracle, so I'll move forward with that for now. Many thanks to you both.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

Thanks for the feedback! 🙂

In future posts, mention that you need the code to work in Oracle too and you will get more vaild responses, as we had no knowledge that the code should work in ORACLE.

Thanks,

Adam

My blog: http://jahaines.blogspot.com