从自引用表中获取Cat1/Cat2/Cat3字符串

Question

我试图从一个自引用表中产生一个类似于Cat1/Cat2/Cat3等的字符串。

数据如下所示：

CategoryTable

CategoryID，名称，ParentID

1，根，0

2，Cat1，1

3，Cat2，2

4，Cat3，3

5，Cat4，1

6，Cat5，5

我想让步：

根目录/Cat1/Cat2/Cat3根目录/Cat4/Cat5

如何在sql server中执行此操作？

Answer 1

您可以使用CTE

SQL Fiddler

WITH cte AS (
  SELECT CategoryID,CAST(Name AS VARCHAR(4000)) AS Name 
  FROM Category 
  WHERE ParentID = 0
  UNION ALL
  SELECT c.CategoryID, CAST(e.Name + '/' + c.Name AS VARCHAR(4000))
  FROM cte e
  INNER JOIN Category c 
    ON c.ParentID = e.CategoryID
)
SELECT c1.Name
FrOM cte c1
WHERE NOT EXISTS (SELECT 1 FROM cte c2 where c1.name <> c2.name AND c2.name like c1.NAME + '%')

Answer 2

试试这个：

DECLARE @str VARCHAR(255) --Or whatever length you need
SET @str = ''

SELECT @str = @str + [Name] + '/' FROM CategoryTable

SET @str = SUBSTRING(@str,0,LEN(@str)) -- remove the trailing '/'
PRINT @str