Sql Server Recursive parent/child same table using same field

Question

I need to get parent and all children from same table...

When run this query:

select location, children  from lochierarchy where parent ='HSAGCF'

This result:

Location    Children
--------    ------------
HSAGCFSMF   1
HSAGEE      1
HSAGGAI     0
HSAGPO      1
HSAGSA      1
HSAGSACC    1
HSAGSAFR    0
HSAGSARV    0
HSAGSASG    0
HSAGSC      1
HSAGSD      1
HSAGSI      1
HSAGSO      1
HSAGSR      0
HSAGST      0
HSAGSTTO    0

When locations has children = 1 has more child in hierarchy

How do I do a recursive that get each location this query above where children = 1 and run the query again like this:

select location 
  from lochierarchy 
 where parent in ( 'HSAGCFSMF', 'HSAGEE', 'HSAGGAI', 'HSAGPO', 'HSAGSA',
                   'HSAGSACC','HSAGSAFR', 'HSAGSARV', 'HSAGSASG', 'HSAGSC',
                   'HSAGSD', 'HSAGSI', 'HSAGSO', 'HSAGSR', 'HSAGST', 
                   'HSAGSTTO', 'HSAGSV', 'HSAGU1', 'HSAGU2', 'HSAGU3', 'HSAGU4')


locations     Children
----------    ------------
HSAGCFSMF       1
HSAGEE          1
HSAGGAI         0
HSAGPO          1
HSAGSA          1
HSAGSACC        1
HSAGSAFR        0
HSAGSARV        0
HSAGSASG        0
HSAGSC          1
HSAGSD          1
HSAGSI          1
HSAGSO          1
HSAGSR          0
HSAGST          0
HSAGSTTO        0
HSAGSV          0

This recursive needs to be repeted until children the all locations be =0

Answer 1

If you're on SQL 2005 or greater, you can use a recursive common table expression. Something like this:

declare @parent varchar(20) = 'HSAGCF';

with cte as (
   select location, parent 
   from lochierarchy
   where location = @parent

   union all

   select c.location, c.parent
   from lochierarchy as c
   join cte as p
      on c.parent = p.location
)
select *
from cte;

If you want a path to the leaf nodes in your tree, I leave that as an exercise for the reader.