我有這張表的目的是擁有多層次的位置：位置內的位置。一直在嘗試無濟于事。感謝你們中的任何人可以提供幫助。
| 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 |
uj5u.com熱心網友回復：
您可以使用 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/414975.html
標籤：
上一篇：根據分隔符拆分兩列