朝相反的方向移动。选择必要的项目后,将FOR XML子句与pattern一起使用以反斜杠分隔的有效值列表。
DECLARE @MyItemMapping_MyItemId int = 4
;WITH cte AS
(
SELECT MyItemMapping_MyItemId, MyItemMapping_MyItemParentId, 1 AS rn
FROM MyItemMapping
Where MyItemMapping_MyItemId = @MyItemMapping_MyItemId
UNION ALL
SELECT m.MyItemMapping_MyItemId, m.MyItemMapping_MyItemParentId, rn + 1
FROM MyItemMapping m JOIN cte c ON c.MyItemMapping_MyItemParentId = m.MyItemMapping_MyItemId
)
SELECT STUFF((SELECT '/' + m.MyItem_Title
FROM cte c JOIN MyItem m
ON c.MyItemMapping_MyItemId = m.MyItem_MyItemId
ORDER BY c.rn DESC
FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') AS pathFolder
pathFolder
结果:
PathFolder
Desktop/Workspace/Folder1/Folder2
关于 演示****