Hierarchy of Employee "names" with CTE: Cannot create a correct version

Question

I am trying to create the correct structure of my sql (CTE) query, but I can not. This is a version I want to have correct version.

And this is my current version: what i have now. As you can see there are duplicates and i have no idea how to get rid of them. Futhermore the structure is wrong. I already tried to look for solutions like here but was unable to come up with solution. CTE query doesn't print the right structure?

My Employee table looks like this http://sqlfiddle.com/#!6/428d2/2

Here is my SQL query I use to create the correct name column: http://sqlfiddle.com/#!6/bf4c1/4

I use MsSQL 2012, so feel free to use newest fuctions etc. Any help appreciated.

• Philᵀᴹ: If you specify the root node in the CTE, it works ok. Fiddle: sqlfiddle.com/#!6/bf4c1/9
• johnmalc: Hi @Phil. I thank you very much. Didn't know that at all. Thanks again. Please post your answer in "as an answer" and not a comment and will give "+".

Answer 1

If you specify the root node in the CTE, it works ok.

WITH Empl_Tab( Id ,
 ParentId ,
 LEVEL,
 [Order]
) AS ( SELECT Employee.[EMPl Id] , Employee.[reports to the Boss] ,
 0 AS LEVEL ,
 CONVERT([varchar](MAX), Employee.[EMPl Id]) AS [Order]
 FROM Employee
where [reports to the Boss] = 1
 UNION ALL
 SELECT Employee.[EMPl Id] ,
 Employee.[reports to the Boss] ,
 Empl_Tab.LEVEL+1 AS LEVEL ,
 Empl_Tab.[Order] + CONVERT([varchar](30), Employee.[EMPl Id]) AS [Order]
 FROM
 Employee INNER JOIN Empl_Tab
 ON Empl_Tab.Id = Employee.[reports to the Boss] 
 )
SELECT REPLICATE( '.' ,Empl_Tab.Id*1 )+Employee.Name AS Name
 FROM
 Employee INNER JOIN Empl_Tab
 ON Empl_Tab.Id = Employee.[EMPl Id]
 ORDER BY Empl_Tab.[Order]

SQL Fiddle here.

Answer 2

Looking at your code on SQLFiddle, you are on the right track with only a key point missing.

The syntax (from BOL) of a recursive CTE is :

WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name

You are missing creating the base result set - which is the highest level in the hierarchy. This is identified by [reports to the Boss] = 1. So if you might have a CEO that does not report to anyone, he will have it set to NULL.

SELECT Employee.[EMPl Id] , Employee.[reports to the Boss] ,
 0 AS LEVEL ,
 CONVERT([varchar](MAX), Employee.[EMPl Id]) AS [Order]
 FROM Employee
where [reports to the Boss] = 1

The rest of the query in your fiddle looks fine.

To get you the results that you have shown, below will work:

;WITH Empl_Tab( Id ,
 ParentId ,
 LEVEL,
 [Order]
) AS ( SELECT Employee.[EMPl Id] , Employee.[reports to the Boss] ,
 0 AS LEVEL ,
 CONVERT([varchar](MAX), Employee.[EMPl Id]) AS [Order]
 FROM Employee
where [reports to the Boss] = 1
 UNION ALL
 SELECT Employee.[EMPl Id] ,
 Employee.[reports to the Boss] ,
 Empl_Tab.LEVEL+1 AS LEVEL ,
 Empl_Tab.[Order] + CONVERT([varchar](30), Employee.[EMPl Id]) AS [Order]
 FROM
 Employee INNER JOIN Empl_Tab
 ON Empl_Tab.Id = Employee.[reports to the Boss] 
 )
SELECT REPLICATE( ' ' ,Empl_Tab.Id*1 )+Employee.Name AS NAME,
 Id as EMPID,
 ParentId as SUPERVISORFK,
 POSITION
 FROM
 Employee INNER JOIN Empl_Tab
 ON Empl_Tab.Id = Employee.[EMPl Id]
 ORDER BY Empl_Tab.[Order]

enter image description here

Hope that helps to understand.

Also, look at the query that @Phil has on fiddle.

• Kin Shah: @johnmalc NP .. As long as we help each other, its fine. I just made sure that you understood the way CTE works as you were almost close, but missing the key point.
• Philᵀᴹ: Up vote for you Kin :)