需要 SQL 查询 (SQL Server 2005) 的帮助来转换此数据

Question

这是一个简单的表，我有

Category

• CategoryId
• CategoryName
• ParentCategoryId

如您所见，目录表是一个自引用表，因此可以从 0 开始递归，这是假的“根”类别（也可以为空，但事实并非如此）。

示例数据：

1, Apples, 0
5, Yummy, 1
10, Really Yummy, 5
15, Yucky, 0
18, Some Sub Cat, 15
20, Some Deep Sub Cat, 18
25, Some Deep Sub Cat 2, 18

任何类别层次结构的最深深度为 4，我试图获得如下所示的输出：

CatId, [up to 4 deep of category names on the hierarchy in separate columns, or null if none]
1, Apples, NULL, NULL, NULL
5, Apples, Yummy, NULL, NULL
10, Apples, Yummy, Really Yummy, NULL
15, Apples, Yucky, NULL, NULL
18, Apples, Yucky, Some Sub Cat, NULL
20, Apples, Yucky, Some Sub Cat, Some Deep Sub Cat
25, Apples, Yucky, Some Sub Cat, Some Deep Sub Cat 2

此 SQL 很接近，但它向后生成，左对齐

select c1.categoryid, c1.name, c2.name, c3.name, c4.name
from category c1
    left outer join category c2
        on c1.parentcategoryid = c2.categoryid
    left outer join category c3
        on c2.parentcategoryid = c3.categoryid
    left outer join category c4
        on c3.parentcategoryid = c4.categoryid

任何 SQL 天才都有一些好主意吗？

Answer 1

select c1.CategoryId as id,
       c1.CategoryName as n1,
       c2.CategoryName as n2,
       c3.CategoryName as n3,
       c4.CategoryName as n4
from            Category c1
left outer join Category c2 on c2.ParentCategoryId = c1.CategoryId
left outer join Category c3 on c3.ParentCategoryId = c2.CategoryId
left outer join Category c4 on c4.ParentCategoryId = c3.CategoryId
where c1.parentcategoryid = 0;

（根据您的编辑：您刚刚在连接中翻转了 ParentCategoryId 和 CategoryID。）