您可以通过递归cte执行此操作:
DECLARE @t TABLE
(
Name VARCHAR(100) ,
CategoryId INT ,
ParentCategoryId INT
)
INSERT INTO @t
VALUES ( 'Footwear', 93, 0 ),
( 'Men Shoes', 6, 93 ),
( 'Female Shoes', 7, 93 ),
( 'Mobile', 2, 0 ),
( 'Smartphone', 4, 2 );
WITH cte
AS ( SELECT * ,
CAST(CategoryId AS VARCHAR(100)) AS Categories
FROM @t
WHERE ParentCategoryId = 0
UNION ALL
SELECT t.* ,
CAST(CAST(t.CategoryId AS VARCHAR(100)) + ','
+ c.Categories AS VARCHAR(100))
FROM @t t
JOIN cte c ON c.CategoryId = t.ParentCategoryId
)
SELECT *
FROM cte