gopi nath gopi nath - 1 year ago 77
SQL Question

Complex Hieraechical Traversing inside Case statement T-SQL

I am trying to write a complex

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.

A.Parent, A.Child,
CASE A.IsDirector
THEN A.Child
WHEN (A.IsDirector = 'NO' AND A.Parent IS NOT NULL)
THEN A.Parent
CASE WHEN B.IsDirector = 'YES'
THEN B.Parent
END AS Director
WHERE B.Child = A.Parent)
END AS Director,
@Org A
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.!6/b5830

Answer Source

Perhaps with a recursive cte

Declare @YourTable table (Level int,Parent varchar(50),Child varchar(50),IsDirector varchar(50))
Insert into @YourTable values

Declare @Fetch varchar(50)='Kim'

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


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)
From cteHB
Order By 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download