quote:

---

Originally posted by spradhan01

Its not a question though!
The database from which I need to extract the query.

---

declare @emp varchar(40)='a3'

declare @tbl table(Name varchar(20),SSN varchar(20),SupvSSN varchar(20),DepNo int)
insert into @tbl
select 'a','1',null,1 union 
select 'a1','11',1,1 union 
select 'a2','111',11,1 union 
select 'a3','1111',111,1 union 
select 'a4','11111',1111,1 union
select 'b1','2',null,2 union 
select 'b2','22',2,2 union 
select 'b3','222',22,2 

;with cte
as
(
select *,convert(varchar(20),null)s  from @tbl
union all
select t.*,t.SupvSSN from @tbl t inner join cte c on c.SSN =t.SupvSSN
)
,cte1
as
(
		select *,row_number()over(partition by DepNo order by s)rid from
		(
			select distinct * from cte where SupvSSN is  null or s is not null
		)T  
)

select c.Name,c.SupvSSN,c.DepNo from cte1 c where exists

		(
		    select c1.rid-2,DepNo as supid from cte1 c1
			  where c1.Name=@emp and c.DepNo=c1.DepNo and c.rid=c1.rid-2
		)

 order by Name