Refer this
http://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx[
^]
Result :
create table ##table1 (id bigint identity(1,1),case_id bigint,Related_case_id bigint)
insert into ##table1(case_id ,Related_case_id) values(11,22)
insert into ##table1(case_id ,Related_case_id) values(22,33)
insert into ##table1(case_id ,Related_case_id) values(05,47)
insert into ##table1(case_id ,Related_case_id) values(89,33)
insert into ##table1(case_id ,Related_case_id) values(33,44)
insert into ##table1(case_id ,Related_case_id) values(67,56)
select *From ##table1
with cte(id , pid)
as
(select case_id,Related_case_id from ##table1 where case_id=11
union all
select a.pid ,t.Related_case_id from cte as a inner join ##table1 as t on a.pid = t.case_id
)
select *from cte