The Problem
A quite common requirement is to introduce a path column in a recursive CTE query. With a path, I mean a column which gathers information from the previous levels in the same hierarchy. An easy way to do this is to concatenate data from previous levels to the current row.
For example, if you would need to present locations hierarchically, you could have the following table:
CREATE TABLE Locations (
Id int,
ParentId int,
LocationType varchar(100),
LocationName varchar(100)
)
and the data:
INSERT INTO Locations (Id, ParentId, LocationType, LocationName) VALUES
(1, null, 'Continent', 'Europe'),
(2, 1, 'Country', 'France'),
(3, 2, 'City', 'Paris'),
(4, 2, 'City', 'Marseille'),
(5, 2, 'City', 'Lyon'),
(6, 1, 'Country', 'Italy'),
(7, 6, 'City', 'Rome'),
(8, 6, 'City', 'Milan'),
(9, 6, 'City', 'Venice'),
(10, 1, 'Country', 'United Kingdom'),
(11, 10, 'City', 'London'),
(12, 10, 'City', 'Cambridge'),
(13, 10, 'City', 'Bath');
The requirement could contain that you need to show for each city, where it is located (continent and country). We can do this with a small recursive CTE like the following:
WITH Places (Id, ParentId, LocationName, Level, FullLocationType, FullLocationName) AS (
SELECT l.Id,
l.ParentId,
l.LocationName,
1,
l.LocationType,
l.LocationName
FROM Locations l
WHERE l.ParentId IS NULL
UNION ALL
SELECT l.Id,
l.ParentId,
l.LocationName,
p.Level + 1,
CONCAT(p.FullLocationType, ' / ', l.LocationType),
CONCAT(p.FullLocationName, ' / ', l.LocationName)
FROM Locations l
INNER JOIN Places p ON p.Id = l.ParentId
)
SELECT p.Level,
p.FullLocationType,
p.FullLocationName
FROM Places P
ORDER BY p.Id;
But when running this query, you'll end up receiving an error message:
Types don't match between the anchor and the recursive part in
column "FullLocationType" of recursive query "Places".
Why is this? As we can see, both parts of the recursive query contain a varchar column for FullLocationType.
The thing is that even though the actual data type matches, for character types also the length must match. The definition for the column was varchar(100) but the concatenation produces a string with maximum data length.
So an easy fix is to convert the initial data to the same length as the concatenation produces. To do this, you can use CAST and CONVERT functions. Here's the modified query:
WITH Places (Id, ParentId, LocationName, Level, FullLocationType, FullLocationName) AS (
SELECT l.Id,
l.ParentId,
l.LocationName,
1,
CAST(l.LocationType AS varchar(max)),
CAST(l.LocationName AS varchar(max))
FROM Locations l
WHERE l.ParentId IS NULL
UNION ALL
SELECT l.Id,
l.ParentId,
l.LocationName,
p.Level + 1,
CONCAT(p.FullLocationType, ' / ', LocationType),
CONCAT(p.FullLocationName, ' / ', l.LocationName)
FROM Locations l
INNER JOIN Places p ON p.Id = l.ParentId
)
SELECT p.Level,
p.FullLocationType,
p.FullLocationName
FROM Places P
ORDER BY p.Id;
When executed, the query now happily returns the results:
Level FullLocationType FullLocationName
----- -------------------------- -------------------------------------
1 Continent Europe
2 Continent / Country Europe / France
3 Continent / Country / City Europe / France / Paris
3 Continent / Country / City Europe / France / Marseille
3 Continent / Country / City Europe / France / Lyon
2 Continent / Country Europe / Italy
3 Continent / Country / City Europe / Italy / Rome
3 Continent / Country / City Europe / Italy / Milan
3 Continent / Country / City Europe / Italy / Venice
2 Continent / Country Europe / United Kingdom
3 Continent / Country / City Europe / United Kingdom / London
3 Continent / Country / City Europe / United Kingdom / Cambridge
3 Continent / Country / City Europe / United Kingdom / Bath
Points of Interest
An interesting thing is that this problem does not occur with a regular UNION statement. Consider the following example:
SELECT CAST('A' AS varchar(100))
UNION ALL
SELECT CONCAT('A', 'B', 'C');
Without any problems, it returns:
(No column name)
----------------
A
ABC
References
History
- 29th December, 2019: Created
