gopi nath gopi nath - 23 days ago 7
SQL Question

Complex Hieraechical Traversing inside Case statement T-SQL

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 find the director of a person. The result is supposed to be like this if I query for Kim.

Parent Child Director
-------------------------
Mark Kim TimCook


Here is the query that I wrote.

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
WHERE
Child = 'Kim'


The result is

Parent Child Director IsDirector
------------------------------------
Mark Kim Mark NO


Below is the SQL Fiddle. Sorry i don't know how to format the above codes.

http://sqlfiddle.com/#!6/b5830

Answer

Perhaps with 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')

Declare @Fetch varchar(50)='Kim'

;with cteHB as (
      Select Child
            ,Parent
            ,Lvl=1
            ,Director=case when IsDirector='Yes' then Child else '' end
      From   @YourTable 
      Where  Child=@Fetch
      Union  All
      Select R.Child
            ,R.Parent
            ,P.Lvl+1
            ,Director=case when IsDirector='Yes' then R.Child else '' end
      From   @YourTable R
      Join   cteHB P on P.Parent = R.Child)
Select A.*
      ,B.Director
 From  @YourTable A
 Join  cteHB B on (A.Child=@Fetch and Director<>'')

Returns

Level   Parent  Child   IsDirector  Director
4       Mark    Kim     NO          TimCook

Just for fun, to see the path, you could replace the file SELECT with

Select Lvl = Row_Number() over (Order By Lvl Desc)
      ,Child
      ,Parent
      ,Director
From cteHB
Order By 1
Comments