Complex Case statement T-SQL - with Hierarchial traversing.

Question

I am trying to write a complex CASE statement on a hierarchical data set.

This is the table with sample data:

Level  Parent     Child       IsDirector
----------------------------------------
0      NULL       SteveJobs     NO
1      SteveJobs  TimCook       YES
2      TimCook    Greg          NO
3      Greg       Mark          NO
4      Mark       Jack          NO
4      Mark       Kim           NO
4      Mark       Tyler         NO
4      Mark       Emma          NO

I'm trying to write a SQL query to list the director for all the persons. In the above example this one part of a IT team where SteveJobs is the CEO and hence he's not a director. TimCook is the Director and under the director is the manager Greg. Under manager are the Employees.

So I want to write a query to select all people and the related Director Name in the Director column with a condition where if it's a CEO then it should be NULL.

In my real life data, there are several CEO's and there will be multiple director under each CEO and multiple manager under each Director. This is really complicated for me.

The result is supposed to be like this if I write a select *.

Parent     Child        Director  IsDirector
------------------------------------
NULL       SteveJobs    NULL      NO
SteveJobs  TimCook      TimCook   YES
TimCook    Greg         TimCook   NO
Greg       Mark         TimCook   NO
Mark       Jack         TimCook   NO
Mark       Kim          TimCook   NO
Mark       Tyler        TimCook   NO
Mark       Emma         TimCook   NO

And this is the query that i've written to achieve this, but it doesn't work as expected.

SELECT 
    A.Parent, A.Child,
    CASE A.IsDirector 
        WHEN 'YES' 
           THEN A.Child
        WHEN 'NO' 
           THEN CASE 
                   WHEN (A.IsDirector = 'NO' AND A.Parent IS NOT NULL) 
                      THEN A.Parent
                      ELSE (SELECT  
                                CASE WHEN B.IsDirector = 'YES' 
                                        THEN B.Parent
                                END AS Director
                            FROM @Org B 
                            WHERE B.Child = A.Parent) 
                END 
    END AS Director,
    A.IsDirector
FROM
    @Org A

Answer 1

This can be generated with the help of a recursive cte

Declare @YourTable table (Level int,Parent varchar(50),Child varchar(50),IsDirector varchar(50))
Insert into @YourTable values
(0,NULL,'SteveJobs','NO'),
(1,'SteveJobs','TimCook','YES'),
(2,'TimCook','Greg','NO'),
(3,'Greg','Mark','NO'),
(4,'Mark','Jack','NO'),
(4,'Mark','Kim','NO'),
(4,'Mark','Tyler','NO'),
(4,'Mark','Emma','NO')

;with cteP as (
      Select Parent
            ,Child
            ,Director = case when IsDirector='YES' then CHILD else NULL end 
            ,IsDirector
      From   @YourTable 
      Where  Parent is null
      Union  All
      Select r.Parent
            ,r.Child
            ,Director = case when r.IsDirector='YES' then r.CHILD else p.Director end
            ,r.IsDirector
      From   @YourTable r
      Join   cteP p on r.Parent = p.Child)
Select * from cteP

Returns

Answer 2

DECLARE @Table AS TABLE (Level INT, Parent VARCHAR(100), Child VARCHAR(100), IsDirector VARCHAR(3))
INSERT INTO @Table VALUES
(0,NULL,'SteveJobs','NO')
,(1,'SteveJobs','TimCook','YES')
,(2,'TimCook','Greg','NO')
,(3,'Greg','Mark','NO')
,(4,'Mark','Jack','NO')
,(4,'Mark','Kim','NO')
,(4,'Mark','Tyler','NO')
,(4,'Mark','Emma','NO')

;WITH cte AS (
    SELECT
       CEO = t.Child
       ,t.Parent
       ,t.Child
       ,Director = CASE WHEN t.IsDirector = 'YES' THEN t.Child ELSE NULL END
       ,t.IsDirector
    FROM
           @Table t
    WHERE
       t.Parent IS NULL

    UNION ALL

    SELECT
       c.CEO
       ,t.Parent
       ,t.Child
       ,Director = CASE WHEN t.IsDirector = 'YES' THEN t.Child ELSE c.Director END
       ,t.IsDirector
    FROM
       @Table t
       INNER JOIN cte c
       ON c.Child = t.Parent
)

SELECT *
FROM
    cte

Seeing you know who the CEO is (Parent = NULL) start your recursion from the top and walk down.