SQL 2005 or later, CTEs are the standard way to go as per the examples shown.
SQL 2000, you can do it using UDFs -
CREATE FUNCTION udfPersonAndChildren
(
@PersonID int
)
RETURNS @t TABLE (personid int, initials nchar(10), parentid int null)
AS
begin
insert into @t
select * from people p
where personID=@PersonID
while @@rowcount > 0
begin
insert into @t
select p.*
from people p
inner join @t o on p.parentid=o.personid
left join @t o2 on p.personid=o2.personid
where o2.personid is null
end
return
end
(which will work in 2005, it's just not the standard way of doing it. That said, if you find that the easier way to work, run with it)
If you really need to do this in SQL7, you can do roughly the above in a sproc but couldn't select from it - SQL7 doesn't support UDFs.