Recursive query for heirarchal data

Question

I have data stored in a hierarchy. I need to have a query that can show me the hierarchy. So, a Bed is in a Cell, which is on a Floor, in a Building in a Block.

What I need to do is have a way to show a whole location, based on a given Location ID.

A person may be in a Bed, or in a Cell, or on a floor... or in a building. I have that location ID. What I need to do is display a description of where that person is.

For example, the person is in a Cell, which in the database, might be ID 350. I need to be able to build a string of his location, to show something like:

Cell 2, Floor 1, Building 1, Block 2

Or, the user is in Building 1 somewhere, so the ID for Building 1 might be 3. So I need to display Building 1, Block 2.

The two tables. One holds the locations, the other is just a lookup to see the types.

Is there an efficient way to query this sort of data? I may also have another requirement to display the where-abouts of hundreds of people, so the query is basically run a lot of times.

I tried a recursive CTE, but the examples I find only handle one level (I think?).

Maybe a function returning a Table would be the best way? But the issue then is - depending on the 'Level' you start at, there may be less columns.

I need a way to handle this.

CREATE TABLE [dbo].[Location]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [ParentID] Int NULL,
 [LocationTypeID] Int NOT NULL,
 [Description] Varchar(100) NOT NULL
)
ON [PRIMARY]
go

-- Add keys for table dbo.Location

ALTER TABLE [dbo].[Location] ADD CONSTRAINT [pk_location] PRIMARY KEY ([ID])
 ON [PRIMARY]
go


CREATE TABLE [LocationType]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [Description] Varchar(100) NOT NULL
)
ON [PRIMARY]
go

ALTER TABLE [LocationType] ADD CONSTRAINT [pk_location] PRIMARY KEY ([ID])
 ON [PRIMARY]
go

Now we'll populate some lookup data:

INSERT INTO LocationType (Description) VALUES ('Bed')
INSERT INTO LocationType (Description) VALUES ('Cell')
INSERT INTO LocationType (Description) VALUES ('Floor')
INSERT INTO LocationType (Description) VALUES ('Building')
INSERT INTO LocationType (Description) VALUES ('Block')

And then create some location data.

-- Create the root items, which are the blocks. We'll have 2.

INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (NULL, 5, 'Block A') -- 1
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (NULL, 5, 'Block B') -- 2

-- Now add 3 buildings per block.

-- Block A
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (1, 4, 'Building 1') -- 3
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (1, 4, 'Building 2') -- 4
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (1, 4, 'Building 3') -- 5


-- Block B
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (2, 4, 'Building 1') -- 6
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (2, 4, 'Building 2') -- 7
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (2, 4, 'Building 3') -- 8

-- Now add two floors per building.

-- Building 1 Block A
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (3, 3, '1st Floor') -- 9
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (3, 3, '2nd Floor') -- 10

-- Building 2 Block A
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (4, 3, '1st Floor') -- 11
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (4, 3, '2nd Floor') -- 12

-- Building 3 Block A
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (5, 3, '1st Floor') -- 13
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (5, 3, '2nd Floor') -- 14




-- Building 1 Block B
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (6, 3, '1st Floor') -- 15
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (6, 3, '2nd Floor') -- 16

-- Building 2 Block B
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (7, 3, '1st Floor') -- 17
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (7, 3, '2nd Floor') -- 18

-- Building 3 Block B
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (8, 3, '1st Floor') -- 19
INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (8, 3, '2nd Floor') -- 20

-- Now, just a cursor to populate the cells and beds.

DECLARE @ThisID INT
DECLARE @Cntr INT  = 0

DECLARE mycursor CURSOR FOR
SELECT id from Location where LocationTypeID = 3

OPEN mycursor

FETCH NEXT FROM mycursor
INTO @ThisID

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @Cntr = 1
    WHILE(@Cntr < 20)
    BEGIN
        INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (@ThisID, 4, 'Cell ' + CAST(@Cntr AS VARCHAR))  
        SET @Cntr = @Cntr + 1
    END

    FETCH NEXT FROM mycursor
    INTO @ThisID

END

CLOSE mycursor
DEALLOCATE mycursor


-- Now add two beds per cell
DECLARE my_cursor CURSOR FOR
SELECT id from Location where LocationTypeID = 4

OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @ThisID

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (@ThisID, 5, 'Bed 1')   
    INSERT INTO Location (ParentId, LocationTypeID, Description) VALUES (@ThisID, 5, 'Bed 2')   

    FETCH NEXT FROM my_cursor
    INTO @ThisID

END

CLOSE my_cursor
DEALLOCATE my_cursor

Answer 1

Here is a recursive CTE solution:

WITH rCTE AS(
    SELECT
        l.*, CAST(l.Description AS VARCHAR(MAX)) AS FullLoc
    FROM Location l
    INNER JOIN LocationType  lt ON lt.ID = l.LocationTypeID
    WHERE l.ParentID IS NULL

    UNION ALL

    SELECT
        l.*, CAST(l.Description AS VARCHAR(MAX)) + ', ' + r.FullLoc
    FROM Location l
    INNER JOIN LocationType  lt ON lt.ID = l.LocationTypeID
    INNER JOIN rCTE r
        ON r.ID = l.ParentID
)
SELECT
    ID, FullLoc
FROM rCTE 
WHERE ID IN(21, 111, 190)
ORDER BY ID
OPTION (MAXRECURSION 0)

Answer 2

Simplest way to solve this is by using LEFT JOIN..but it become tricky when hierarchy level is unknown or can become very deep.. if hierarchy level can be predetermined say 6 then this SQL code would work

SELECT
    L0.ID,
    L0.[Description] + 
    ISNULL(', ' + L1.[Description], '')  +
    ISNULL(', ' + L2.[Description], '')  +
    ISNULL(', ' + L3.[Description], '')  +
    ISNULL(', ' + L4.[Description], '')  +
    ISNULL(', ' + L5.[Description], '') FullLocation
FROM Location L0
    LEFT JOIN Location L1 ON L1.ID = L0.[ParentID]
    LEFT JOIN Location L2 ON L2.ID = L1.[ParentID]
    LEFT JOIN Location L3 ON L3.ID = L2.[ParentID]
    LEFT JOIN Location L4 ON L4.ID = L3.[ParentID]
    LEFT JOIN Location L5 ON L5.ID = L4.[ParentID]
WHERE
    L0.ID IN (21, 111, 190)

Result

╔═════╦═════════════════════════════════════════╗
║ ID  ║              FullLocation               ║
╠═════╬═════════════════════════════════════════╣
║  21 ║ Cell 1, 1st Floor, Building 1, Block A  ║
║ 111 ║ Cell 15, 1st Floor, Building 3, Block A ║
║ 190 ║ Cell 18, 1st Floor, Building 2, Block B ║
╚═════╩═════════════════════════════════════════╝