Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
emilly
Starting Member
4 Posts |
 Posted - 2010-10-11 : 16:54:40
|
| I am trying create a SQL extract file that has the following layout below.User UserBoss Level1 Level2 Level3 Level4 Level5John JohnBob John John BobTom Bob John Bob TomDoug Tom John Bob Tom DougBrad Doug John Bob Tom Doug BradLee John John LeeSue Lee John Lee SueThis listing doesn't have to be sorted in this order and anyone in the listing could have multiple people reporting to them. Any ideas?Currently, I have a sql recursive query (plus case statements) working that ranks employees and then puts them in the levels hierarchy as described above. However, I can only get the level hierarchy to work for Level 1 & 2, or Level 2 & 3, Level 3 & 4, etc. I am wondering how to add on to this to complete the requested output. A co-worker has suggested creating a procedure or using a temp db? Please see my recursive query and case statements below. Also below are the output of this query.WITH EmployeesCTE AS( SELECT 0 AS Rank, EI.EmployeeNumber, EI.DisplayName, EI.ManagerEmployeeNumber, EI.ManagerName, EI.Company, EI.Division, EI.Department, EI.JobTitle FROM UltiPro.NoodleStream_EmployeeInfoForHierarchy EI WHERE EI.ManagerEmployeeNumber = '999999' UNION ALL SELECT Rank + 1, EI.EmployeeNumber, EI.DisplayName, EI.ManagerEmployeeNumber, EI.ManagerName, EI.Company, EI.Division, EI.Department, EI.JobTitle FROM UltiPro.NoodleStream_EmployeeInfoForHierarchy EI JOIN EmployeesCTE ON EI.ManagerEmployeeNumber = EmployeesCTE.EmployeeNumber)SELECT *, CASE WHEN Rank = 1 THEN DisplayName WHEN Rank = 2 THEN ManagerName END AS Level1, CASE WHEN Rank = 2 THEN DisplayName WHEN Rank = 3 THEN ManagerName END AS Level2, CASE WHEN Rank = 3 THEN DisplayName WHEN Rank = 4 THEN ManagerName END AS Level3, CASE WHEN Rank = 4 THEN DisplayName WHEN Rank = 5 THEN ManagerName END AS Level4, CASE WHEN Rank = 5 THEN DisplayName WHEN Rank = 6 THEN ManagerName END AS Level5, CASE WHEN Rank = 6 THEN DisplayName WHEN Rank = 7 THEN ManagerName END AS Level6, CASE WHEN Rank = 7 THEN DisplayName END AS Level7FROM EmployeesCTEUser UserBoss Level1 Level2 Level3 Level4 Level5John JohnBob John John BobTom Bob Bob TomDoug Tom Tom DougBrad Doug Doug BradLee John John LeeSue Lee Lee SueI appreciate any help! Thanks! |
|
|
emilly
Starting Member
4 Posts |
Posted - 2010-10-11 : 17:00:07
|
| I apologize, I left out part of my results I'm getting with the current query. The first column contains rank.Rank User UserBoss Level1 Level2 Level3 Level4 Level51 John John2 Bob John John Bob3 Tom Bob John Bob Tom4 Doug Tom John Bob Tom Doug5 Brad Doug John Bob Tom Doug Brad2 Lee John John Lee3 Sue Lee John Lee Sue |
 |
|
|
|
|
|
|
|
Hierarchy Employee Listing