Multilevel Location - Output as delimited series-CodePudding

I have this table with the intention of having multi-levels of locations: Location within a location. Been trying to no avail. Appreciate if any of you can help.

| Location_ID | Location       | Location_ID1 |
| ----------- | -------------- | ------------ |
| 10001       | Warehouse A    |              |
| 10002       | Warehouse B    |              |
| 10003       | Rack A         | 10001        |
| 10004       | Rack B         | 10001        |
| 10005       | Top Shelf      | 10003        |
| 10006       | Bottom Shelf   | 10003        |

I want to select all of the Locations and populate a dropdown list with this value-label pair

|Value        | Label                              |
|-------------|------------------------------------|
|10001        |Warehouse A                         |
|10003        |Warehouse A > Rack A                |
|10005        |Warehouse A > Rack A > Top Shelf    |
|10006        |Warehouse A > Rack A > Bottom Shelf |
|10004        |Warehouse A > Rack B                |
|10002        |Warehouse B                         |

CodePudding user response：

You can use an rCTE to iterate through the data here, and also delimit your data with a little concatenation:

SELECT *
INTO dbo.YourTable
FROM (VALUES(10001,'Warehouse A',NULL ),
            (10002,'Warehouse B',NULL ),
            (10003,'Rack A',10001),
            (10004,'Rack B',10001),
            (10005,'Top Shelf',10003),
            (10006,'Bottom Shelf',10003))V(Location_ID,Location,Location_ID1);
GO
WITH rCTE AS(
    SELECT Location_ID,
           Location,
           Location_ID1,
           CONVERT(varchar(500),Location) AS Label
    FROM dbo.YourTable YT
    WHERE Location_ID1 IS NULL
    UNION ALL
    SELECT YT.Location_ID,
           YT.Location,
           YT.Location_ID1,
           CONVERT(varchar(500),r.Label   ' > '   YT.Location) AS Label
    FROM dbo.YourTable YT
         JOIN rCTE r ON YT.Location_ID1 = r.Location_ID)
SELECT *
FROM rCTe;
GO

DROP TABLE dbo.YourTable