多级位置-输出为分隔序列

Question

我有这个表的目的是拥有多个级别的位置：一个位置中的位置。一直在尝试，但没有成功。如果你们中有人能帮忙，我将不胜感激。

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

我想选择所有的位置，并用这个值标签对填充下拉列表

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

Answer 1

您可以使用rCTE来迭代此处的数据，还可以使用一点串联来定界数据：

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