Introduction
This challenge is more about processing HIERARCHIES.
The problem is all about identifying all the employees directly or indirectly to whom the given Employee reports to. The challenge is to write a query that can take a Employee Id as a parameter and list all the employees to whom the Employee is reporting, directly or indirectly.
Sample Data
EmpRecord
-----------------------------------------------------------------
If the parameter contains 9 the query should return:
Expected Results
Level Path Hierarchy
----- ----------------------------------------- -----------------
0 Jiya Devi/Deepak Kr. Goyal/Niladri Biswas Niladri Biswas
1 Jiya Devi/Deepak Kr. Goyal Deepak Kr. Goyal
2 Jiya Devi Jiya Devi
Your query should ideally look like the following:
DECLARE @EmpID VARCHAR(20)
SELECT @EmpID = 9
Rules
- The program should run in SQL SERVER 2005+.
- Column names should respect the desired output shown.
- Result must be sorted in ascending order of Level.
- Hierarchy column should display parents starting from the topmost position to the
child sought for.
Sample Script
Use the following script to generate the sample data.
DECLARE @Employees TABLE(EmpRecord XML)
INSERT INTO @Employees
SELECT '
'
SELECT * FROM @Employees
Restrictions
- The solution should be a single query that starts with a "SELECT" or “;WITH”.
Notes
Tags:Puzzles, TSQL Beginners Challenge, TC, TSQL Beginners Challenge 17