Recursive Parent-Child Location Relationship Cache as View Here is my data structure:

CREATE TABLE Locations(
    LocID int IDENTITY(1,1) NOT NULL,
    LocationTypeID int NOT NULL,
    ParentID int NULL,
    LocNum varchar(50) NULL,
    LocName varchar(250) NULL,
)


CREATE TABLE LocationTypes(
    LocationTypeID int IDENTITY(1,1) NOT NULL,
    TypeName varchar(50) NOT NULL
)


The LocationTypes table contains the following values:

+----------------+----------+
| LocationTypeID | TypeName |
+----------------+----------+
|              1 | Campus   |
|              2 | Building |
|              3 | Area     |
|              4 | Floor    |
|              5 | Room     |
+----------------+----------+


The Locations table contains a bunch of locations, some where the ParentID is null (Campuses), and others that have a ParentID. There are 5 levels of hierarchy to my data structure, indicated in the LocationTypes table. Campus (1) being the least specific, and Room(5) being the most specific. In my database I have other tables which reference the LocID, most often these tables are referencing Locations with a LocationTypeID of 5, but not always.

I have a need to get the names of every Location in the hierarchy for display to the end-user.

Ultimately what I want to get is a view (or cached table) with the following structure:

+-------+---------------+-----------------+-------------+--------------+-------------+
| LocID | CampusLocName | BuildingLocName | AreaLocName | FloorLocName | RoomLocName |
+-------+---------------+-----------------+-------------+--------------+-------------+


I realize that some of these columns will end up being null, for Locations with a LocationTypeID of anything other than 5, and that is perfectly fine.

I know that I probably need a recursive common table expression, I just don't really know where to start.

Basically the pseudo-code logic for this is:


Foreach Location in the LocationsTable
Get the Parent Location of each Location, continue recursively until there are no more parents
Store this cached data in a view, so that it can be easily LEFT JOINed on any other table that references a LocID


I hope that all makes sense. Thanks anyone that can help me.
I believe you can achieve the desired result with this query:

;WITH CTE_Locations (LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName)
AS
(
    SELECT LocID, 
        LocName AS CampusLocName, 
        CONVERT(varchar(250), NULL) AS BuildingLocName, 
        CONVERT(varchar(250), NULL) AS AreaLocName, 
        CONVERT(varchar(250), NULL) AS FloorLocName, 
        CONVERT(varchar(250), NULL) AS RoomLocName
    FROM Locations
    WHERE ParentID IS NULL

    UNION ALL

    SELECT L.LocID, 
        CampusLocName, 
        IIF(L.LocationTypeID = 2, LocName, BuildingLocName) AS BuildingLocName, 
        IIF(L.LocationTypeID = 3, LocName, AreaLocName) AS AreaLocName, 
        IIF(L.LocationTypeID = 4, LocName, FloorLocName) AS FloorLocName, 
        IIF(L.LocationTypeID = 5, LocName, RoomLocName) AS RoomLocName
    FROM Locations L
        INNER JOIN CTE_Locations CTE ON L.ParentID = CTE.LocID 
)
SELECT LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName
FROM CTE_Locations;


According to the WITH common_table_expression doc:


  This clause can also be used in a CREATE VIEW statement as part of its
  defining SELECT statement.