sql-server - 将此分层表非规范化为平面表的最快/最简单的方法是什么？

Question

我有以下层次结构表:

Table Category:
CategoryId, ParentCategoryId, CategoryName
1, null, SomeRoot
2, 1, SomeChild
3, 2, SomeGrandchild
4, 3, SomeGreatGrandchild

(请注意，此示例数据不包含早于级别 4 的节点上的叶子，但这是可能的)。如果相关的话，数据永远不会比第 4 级更深。我想将其转换/旋转到这个固定的 4 级显示

CatId, Name1, Name2, Name3, Name4
1, SomeRoot, null, null, null
2, SomeRoot, SomeChild, null, null
3, SomeRoot, SomeChild, SomeGrandchild, null
4, SomeRoot, SomeChild, SomeGrandchild, SomeGreatGrandchild

我已经对类别表进行了 4 次左外连接，并构建了一个巨大的 case 语句来检测用于 ID 字段的级别，但这不包括空行......有什么想法吗？帮助!

Answer 1

这可能不是最有效的查询，但它是最容易编码的:

declare @YourTable table (CategoryId int primary key, ParentCategoryId int , CategoryName varchar(50))

INSERT INTO @YourTable VALUES (1, null, 'SomeRoot')
INSERT INTO @YourTable VALUES (2, 1, 'SomeChild')
INSERT INTO @YourTable VALUES (3, 2, 'SomeGrandchild')
INSERT INTO @YourTable VALUES (4, 3, 'SomeGreatGrandchild')

INSERT INTO @YourTable VALUES (10, null, 'X_SomeRoot')
INSERT INTO @YourTable VALUES (20, 10, 'X_SomeChild')
INSERT INTO @YourTable VALUES (30, 20, 'X_SomeGrandchild')


Select
    c1.CategoryId, c1.CategoryName, c2.CategoryName, c3.CategoryName, c4.CategoryName
    From @YourTable          c1
        INNER JOIN @YourTable c2 On c1.CategoryId = c2.ParentCategoryId
        INNER JOIN @YourTable c3 On c2.CategoryId = c3.ParentCategoryId
        INNER JOIN @YourTable c4 On c3.CategoryId = c4.ParentCategoryId
    WHERE c1.ParentCategoryId IS NULL 
UNION
Select
    c1.CategoryId, c1.CategoryName, c2.CategoryName, c3.CategoryName, NULL
    From @YourTable          c1
        INNER JOIN @YourTable c2 On c1.CategoryId = c2.ParentCategoryId
        INNER JOIN @YourTable c3 On c2.CategoryId = c3.ParentCategoryId
    WHERE c1.ParentCategoryId IS NULL
UNION
Select
    c1.CategoryId, c1.CategoryName, c2.CategoryName, NULL, NULL
    From @YourTable          c1
        INNER JOIN @YourTable c2 On c1.CategoryId = c2.ParentCategoryId
    WHERE c1.ParentCategoryId IS NULL
UNION
Select
    c1.CategoryId, c1.CategoryName, NULL, NULL, NULL
    From @YourTable          c1
    WHERE c1.ParentCategoryId IS NULL
ORDER BY 2,3,4,5

输出:

SortB CategoryId  CategoryName CategoryName  CategoryName      CategoryName
----- ----------- ------------ ------------- ----------------- --------------------
1     1           SomeRoot     NULL          NULL              NULL
2     1           SomeRoot     SomeChild     NULL              NULL
3     1           SomeRoot     SomeChild     SomeGrandchild    NULL
4     1           SomeRoot     SomeChild     SomeGrandchild    SomeGreatGrandchild
1     10          X_SomeRoot   NULL          NULL              NULL
2     10          X_SomeRoot   X_SomeChild   NULL              NULL
3     10          X_SomeRoot   X_SomeChild   X_SomeGrandchild  NULL

(7 row(s) affected)