Here is my data structure:

    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)
    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


    SELECT L.LocID, 
        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.

  • could this be included in a view? – David Allen May 20 '20 at 1:54
  • Yes @DavidAllen, just remove the ";" from the beginning. – Ronaldo May 20 '20 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.