tsql breadcrumb查询

Question

​

我有以下CTE查询

;WITH cte AS (
    SELECT 0 AS lvl, id, catName, parent,
        CAST(id AS VARCHAR(128)) AS Sort
    FROM CategoriesMap WHERE id =2
    UNION ALL
    SELECT p.lvl + 1, c.id, c.catName, c.parent,
        CAST(CAST(c.id AS VARCHAR) + '_' + p.Sort   AS VARCHAR(128))
    FROM CategoriesMap c
    INNER JOIN cte p ON p.parent = c.id
)
select * from cte

Sort(Tree)列的输出如下(对于两行)...

2
1_2

其中，2是类别TVs，1_2表示这是树映射(1: Internet Tv, 2 = Jadoo Tv)

现在我可以返回类别名称和类别代码了吗？

就像这样

2:Jadoo Tc

1_Internet Tv: 2_Jadoo Tv

谢谢

Answer 1

;WITH cte AS (
    SELECT 0 AS lvl, id, catName, parent,
        CAST(CAST(id AS VARCHAR(128)) + ':' + catName AS VARCHAR(128)) AS Sort
    FROM CategoriesMap WHERE id =2
    UNION ALL
    SELECT p.lvl + 1, c.id, c.catName, c.parent,
        CAST(CAST(c.id AS VARCHAR) + ':' + c.catName + '_' + p.Sort AS VARCHAR(128))
    FROM CategoriesMap c
    INNER JOIN cte p ON p.parent = c.id
)
select * from cte

Answer 2

;WITH cte AS (
    SELECT 0 AS lvl, id, catName, parent,
        CAST(id AS VARCHAR(128)) AS Sort
    FROM CategoriesMap WHERE id =2
    UNION ALL
    SELECT p.lvl + 1, c.id, c.catName, c.parent,
        CAST(CAST(c.id AS VARCHAR) + '_'+c.catName+':' + p.Sort   AS VARCHAR(128))
    FROM CategoriesMap c
    INNER JOIN cte p ON p.parent = c.id
)
select * from cte