sql server 2008 - Recursive select in SQL

Question

I have an issue I just can't get my head around. I know what I want, just simply can't get it out on the screen. What I have is a table looking like this:

Id, PK UniqueIdentifier, NotNull
Name, nvarchar(255), NotNull
ParentId, UniqueIdentifier, Null

ParentId have a FK to Id.

What I want to accomplish is to get a flat list of all the id's below the Id I pass in.

example:

1   TestName1    NULL
2   TestName2    1
3   TestName3    2
4   TestName4    NULL
5   TestName5    1

The tree would look like this:

-1
  -> -2
       -> -3
  -> -5
-4

If I now ask for 4, I would only get 4 back, but if I ask for 1 I would get 1, 2, 3 and 5. If I ask for 2, I would get 2 and 3 and so on.

Is there anyone who can point me in the right direction. My brain is fried so I appreciate all help I can get.

Answer 1

declare @T table(
  Id int primary key,
  Name nvarchar(255) not null,
  ParentId int)

insert into @T values
(1,   'TestName1',    NULL),
(2,   'TestName2',    1),
(3,   'TestName3',    2),
(4,   'TestName4',    NULL),
(5,   'TestName5',    1)

declare @Id int = 1

;with cte as
(  
  select T.*
  from @T as T
  where T.Id = @Id
  union all
  select T.*
  from @T as T
    inner join cte as C
      on T.ParentId = C.Id
)
select *
from cte      

Result

Id          Name                 ParentId
----------- -------------------- -----------
1           TestName1            NULL
2           TestName2            1
5           TestName5            1
3           TestName3            2