Getting "fully qualified" location names from a parent-child table

Question

I have a table that contains place names, and their parent place names. Parents and children have numeric ids as well. For example:

id   name           parent_id  parent_name
---- ------         ---------- -----------
 1   Chicago         2         Cook
 2   Cook            3         Illinois
 3   Illinois        4         United States
 4   United States   NULL      NULL

What I want is to populate another field with the "fully qualified" place name. Which is to say something like this:

id   name           parent_id  parent_name      FQName  
---- ------         ---------- --------------   --------------------------------------
 1   Chicago         2         Cook             Chicago, Cook, Illinois, United States
 2   Cook            3         Illinois         Cook, Illinois, United States
 3   Illinois        4         United States    Illinois, United States
 4   United States   NULL      NULL             United States

It's recursive, so I thought of using a CTE. But since there are n levels, and n can differ depending on location (for example, the longest I'd see in a small country like Israel would be Karmiel, North, Israel), I don't know how to work it. All of the examples I've seen of CTEs go top down, because the top is known quantity (parent = NULL).

I'm sure there's something obvious that I'm missing, but I can't put my finger on it.

I'm stuck with a version of SQL Server that doesn't support STRING_AGG, so I can't use that, either.

Answer 1

SQL Server 2016/2014 solution using recursive CTE

WITH CTE as (
  SELECT pn.id, 
         pn.name, 
         pn.parent_id, 
         cast(NULL as VARCHAR(512)) as ParentName,
         pn.name as FQName
  FROM placenames pn
  WHERE NOT EXISTS 
        (SELECT 1 FROM placenames as pn2 WHERE  pn2.id = pn.parent_id)

  UNION ALL

  SELECT b.id, 
         b.name, 
         b.parent_id, 
         a.Name as ParentName, 
         cast(b.name + ', ' + a.FQName as VARCHAR(512)) as FQName
  FROM CTE a 
  INNER JOIN placenames b ON a.id=b.parent_id
)
SELECT * FROM CTE
ORDER BY CTE.id

fiddle

id	name	parent_id	ParentName	FQName
1	Chicago	2	Cook	Chicago, Cook, Illinois, United States
2	Cook	3	Illinois	Cook, Illinois, United States
3	Illinois	4	United States	Illinois, United States
4	United States	null	null	United States
5	Karmiel	6	North	Karmiel, North, Israel
6	North	7	Israel	North, Israel
7	Israel	null	null	Israel