I am struggling with preparing one SQL query that should return the expected data in one shot.
My requirement is to get the data from SQl table called JobCollection in such a way that which will return a data as highlighted in green border in below image.
This data is organised in Parent Child way. As you see below,
Conditions:
As highlighted in green border, JCId 8,9,10,11 and 12 are not parent of the any record
Also the green border highlights super child of JCId 1 and not JCId 2
Please note, this is an example and we can not use to stored procedure or cursor. And the hierarchy level is undefined. It can be anything.
Update:
One more Example
I want to get only those records highlighted in red. As you see the green border tells that those are the super child of every record but the Red highlights the Super child records of JCId 1
From bottom of heart I request to everyone please read the question carefully and understand the pain in it before down voting to the question. It is really hard for me to get the expected result
Perhaps a little more than you need, but you could slim it down if so desired.
The trick here is to use the Range Keys R1/R2.
Declare @YourTable table (JCId int,JCParentId int,JCName varchar(50))
Insert into @YourTable values
( 1, NULL,'A')
,( 2, NULL,'B')
,( 3, 1 ,'A1')
,( 4, 1 ,'A2')
,( 5, 1 ,'A3')
,( 6, 2 ,'B1')
,( 7, 2 ,'B2')
,( 8, 3 ,'A11')
,( 9, 3 ,'A12')
,(10, 4 ,'A21')
,(11, 5 ,'A31')
,(12, 5 ,'A32')
,(13, 6 ,'B11')
,(14, 6 ,'B12')
,(15, 7 ,'B21')
,(16, 7 ,'V22')
Declare @Top int = 1 --null --<< Sets top of Hier Try 3
Declare @Nest varchar(25) = '|-----' --<< Optional: Added for readability
;with cteP as (
Select Seq = cast(10000+Row_Number() over (Order by JCName) as varchar(500))
,JCId
,JCParentId
,Lvl=1
,JCName
From @YourTable
Where IsNull(@Top,-1) = case when @Top is null then isnull(JCParentId ,-1) else JCId end
Union All
Select Seq = cast(concat(p.Seq,'.',10000+Row_Number() over (Order by r.JCName)) as varchar(500))
,r.JCId
,r.JCParentId
,p.Lvl+1
,r.JCName
From @YourTable r
Join cteP p on r.JCParentId = p.JCId)
,cteR1 as (Select *,R1=Row_Number() over (Order By Seq) From cteP)
,cteR2 as (Select A.JCId,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.JCId )
Select A.R1
,B.R2
,A.JCId
,A.JCParentId
,A.Lvl
,JCName = Replicate(@Nest,A.Lvl-1) + A.JCName
From cteR1 A
Join cteR2 B on A.JCId=B.JCId
and R1=R2
Returns
Full Hierarchy when @Top = NULL and you remove the final and R1=R2