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.
4
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 |
A recursive CTE would be the correct general solution. However, if you know for certain that there is a maximum number of levels, you can use a series of LEFT JOINs and the CONCAT_WS() function to build your fully-qualified name.
SELECT
P1.*,
CONCAT_WS(', ', P1.name, P2.name, P3.name, P4.name, P5.name, P6.name) AS FQName
FROM Place P1
LEFT JOIN Place P2 ON P2.id = P1.parent_id
LEFT JOIN Place P3 ON P3.id = P2.parent_id
LEFT JOIN Place P4 ON P4.id = P3.parent_id
LEFT JOIN Place P5 ON P5.id = P4.parent_id
LEFT JOIN Place P6 ON P6.id = P5.parent_id
ORDER BY P1.id
For SQL Server versions prior to 2017 that do not support CONCAT_WS(), you can use the CONCAT() function as follows:
CONCAT(P1.name, ', ' + P2.name, ', ' + P3.name, ', ' + P4.name,
', ' + P5.name, ', ' + P6.name) AS FQName
Null values will be quietly ignored.
| 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 |
See this db<>fiddle for a demo.
Side note: Normally, you would not store parent_name in the child row, since it is redundant with the parent row and can open up the possibilities of data inconsistencies.
1