How to write a nested query for this table?

Question

Imagine that I have a table like the below table in my database, how can I write a query that returns all of the places that are a part of the place that I use as input for this query?

For example, if I send:

(this is the input)   (I want this result)
1 (Europe)            France, Italy, Paris, PartA, PartAA
3 (France)            Paris, PartA, PartAA
4 (Paris)             PartA, PartAA

Answer 1

Recursive CTE to build a subtree with aggregating all places to the single line.

with placesreq(code, place, parent, depth)
as
(
  select code, place, parentCode, 0 as depth
  from places
  where place = 'France'

  union all

  select p.code, p.place, p.parentCode, r.depth+1
  from places as p
    join placesreq as r  on r.code = p.parentCode
)
select place+',' as 'data()'
from placesreq
where depth > 0
for xml path('')

The only small problem is a comma after the last place in the list. You can get rid of it in the query or later in the code.

Answer 2

As suggested by Martin Smith I had a look at recursive CTEs and this is what I came up with:

with place_hierarchy (code, place, parent_code)
as (
    select p.code, p.place, p.parent_code
    from places p
    where p.code = :code
    union all
    select p.code, p.place, p.parent_code
    from places p
    inner join place_hierarchy ph on p.parent_code = ph.code
)
select ph.place
from place_hierarchy ph

It's completely untested, though.