Required help/advise on Recursive Queries Using CTE

Question

I'm trying to get repeated employees (Managers - director - CEO to have full hierarchy) with DepartmentCode similar to Lvl column from Recursive query result.

Found couple of existing Q&A but those are not seem to fit with my case. The aim is to have full hierarchy until the root node (CEO) when the result data filtered with any DepartmentCode. When i tried with following recursive CTE, it looks perfect with Lvl column i.e. when data filtered any ID of Lvl column it gets Employees, Mangers, Directors and CEO (the full hierachy chain).

I'm wondering if the same can be applied for DepartmentCode column using Recursive CTE or using some other approach. Any advise would be appreciable!

Query that i tried, result is here.. (I'm fine with the duplicate values that appears in result)

WITH CTE AS  
        (
            SELECT E.EmployeeID, E.EmpName, E.Title, DepartmentCode, '   ' DepartTest, E.ManagerID, 1 as Lvl
            FROM MyEmployees AS E
        UNION ALL
            SELECT E.EmployeeID, E.EmpName, E.Title, E.DepartmentCode, E.DepartmentCode, E.ManagerID, Lvl + 1
            FROM MyEmployees AS E
                JOIN CTE ON E.EmployeeID = CTE.ManagerID
                --E.ManagerID = CTE.EmployeeID
          ) 
SELECT * FROM CTE
Order by Lvl
go

Sample Data script:

    CREATE TABLE dbo.MyEmployees
    (
        EmployeeID smallint NOT NULL,
        EmpName nvarchar(30)  NOT NULL,
        Title nvarchar(50) NOT NULL,
        DepartmentCode varchar(3),
        ManagerID int NULL,
     CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
    );


    -- Populate the table with values.
    INSERT INTO dbo.MyEmployees 
    (EmployeeID, EmpName, Title, DepartmentCode, ManagerID)
    VALUES 
     (1,   N'Ken',  N'Chief Executive Officer', 'SMG', NULL)
    ,(201, N'Brian',  N'Director', 'OPD',1)
    ,(301, N'Stephen', N'IT Manager', 'ICT',201)
    ,(302, N'Michael', N'IT Position1','ICT',301)
    ,(303, N'Linda', N'IT Position2','ICT', 301)
    ,(401, N'Syed', N'Procurement Position1','PRO',201)
    ,(402, N'Abbas', N'Procurement Position1','PRO',401)
    ,(403, N'Lynn', N'Procurement Position2','PRO',401)
    GO

I'm not sure I understand what you are trying to achieve... basically the lvl is only used to distinguish - as it says - the level of your recursion. If you want to filter by departement code or something, it should be possible to put a corresponding `WHERE` clause inside or outside of your cte. Could you please clarify, how your destination output should look like? — Tyron78, Sep 23 '19 at 13:28
Hi @Tyron78, the ultimate goal is to have Organization chart in Power-BI report which i'm currently working on with custom visual. I believe, the partial result (when use `WHERE` clause within CTE) would not work as source for Power-BI report — Shekar Kola, Sep 23 '19 at 14:27

score 1 · Accepted Answer · edited Sep 24 '19 at 07:42

This code changes may helps You

;WITH CTE
AS
(
SELECT 
         EmployeeID
        ,EmpName
        ,ManagerID
        ,CAST('\'+EmpName AS VARCHAR(100)) AS ManagerName
        ,1 as Lvl

FROM MyEmployees
WHERE managerid IS NULL
union all
SELECT 
         e.EmployeeID
        ,e.EmpName
        , e.ManagerID
         ,CAST(ManagerName+'\'+c.EmpName AS VARCHAR(100)) AS ManagerName
         , Lvl+1
FROM CTE c
INNER JOIN MyEmployees e
ON c.EmployeeID = e.ManagerID

)
SELECT  EmployeeID, 
        EmpName,
        ManagerID,
        RIGHT(ManagerName,LEN(ManagerName)-1) AS ManagerName,
        Lvl
FROM CTE
GO

Result

EmployeeID  EmpName ManagerID   ManagerName                 Lvl
*****************************************************************
1            Ken        NULL    Ken                          1
201          Brian      1       Ken\Ken                      2
301          Stephen    201     Ken\Ken\Brian                3
401          Syed       201     Ken\Ken\Brian                3
402          Abbas      401     Ken\Ken\Brian\Syed           4
403          Lynn       401     Ken\Ken\Brian\Syed           4
302          Michael    301     Ken\Ken\Brian\Stephen        4
303          Linda      301     Ken\Ken\Brian\Stephen        4

Adjusted query (as per comment):

;WITH CTE
AS
(
SELECT 
         EmployeeID
        ,Title
        ,ManagerID
        ,DepartmentCode 
        ,1 as Lvl

FROM MyEmployees
--WHERE managerid IS NULL
union all
SELECT 
         e.EmployeeID
        ,e.Title
        ,e.ManagerID
         ,c.DepartmentCode
         , Lvl+1
FROM CTE c 
INNER JOIN MyEmployees e
ON c.ManagerID = e.EmployeeID

)
SELECT  EmployeeID, 
        Title,
        ManagerID,
        DepartmentCode,
        --RIGHT(ManagerName,LEN(ManagerName)-1) AS ConcatManagerName,
        Lvl
FROM CTE
Order by DepartmentCode

Love it, you're hint made me to achieve what i'm [looking for..](https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=3744dafaf10c46bc04979be4966864fe) — Shekar Kola, Sep 23 '19 at 17:50

Required help/advise on Recursive Queries Using CTE

1 Answers1