SQL Server：如何从单表生成适配多层级的层级组合

I get it—hardcoding joins for each possible hierarchy depth is messy, especially when your data has variable levels. Let's cover two approaches that adapt to any number of levels present in your table.

Approach 1: Recursive CTE for Hierarchy Paths (No Dynamic SQL) #

This method builds a delimited string of the full hierarchy for each id, which is great if you don't need separate columns for each level. It works regardless of how deep your hierarchy goes.

WITH HierarchyCTE AS (
    -- Base case: Start with the top-level nodes (level 1)
    SELECT 
        id,
        CAST(lvl AS INT) AS level_num,
        hier,
        CAST(hier AS NVARCHAR(MAX)) AS full_hierarchy,
        1 AS current_depth
    FROM tmp.tblSample
    WHERE CAST(lvl AS INT) = 1

    UNION ALL

    -- Recursive step: Join to the next level for the same id
    SELECT 
        h.id,
        CAST(s.lvl AS INT) AS level_num,
        s.hier,
        CAST(h.full_hierarchy + ' -> ' + s.hier AS NVARCHAR(MAX)) AS full_hierarchy,
        h.current_depth + 1 AS current_depth
    FROM HierarchyCTE h
    INNER JOIN tmp.tblSample s 
        ON h.id = s.id 
        AND CAST(s.lvl AS INT) = h.current_depth + 1
)
-- Get the complete hierarchy for each id (the deepest entry per id)
SELECT 
    id,
    full_hierarchy,
    current_depth AS max_level
FROM HierarchyCTE
WHERE current_depth = (
    SELECT MAX(CAST(lvl AS INT)) 
    FROM tmp.tblSample 
    WHERE id = HierarchyCTE.id
);

Output Example: #

Approach 2: Dynamic SQL for Level Columns #

If you need separate columns for each level (like your original hardcoded query), use dynamic SQL to automatically generate the necessary joins and columns based on the maximum level in your data. This adapts to any hierarchy depth.

DECLARE @MaxLevel INT = (SELECT MAX(CAST(lvl AS INT)) FROM tmp.tblSample);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @SelectColumns NVARCHAR(MAX) = 't1.id, t1.hier AS Level1';
DECLARE @JoinClauses NVARCHAR(MAX) = '';

-- Build join clauses and select columns for each level beyond 1
IF @MaxLevel >= 2
BEGIN
    DECLARE @i INT = 2;
    WHILE @i <= @MaxLevel
    BEGIN
        SET @SelectColumns += ', t' + CAST(@i AS NVARCHAR) + '.hier AS Level' + CAST(@i AS NVARCHAR);
        SET @JoinClauses += ' LEFT JOIN tmp.tblSample t' + CAST(@i AS NVARCHAR) + 
                        ' ON t1.id = t' + CAST(@i AS NVARCHAR) + '.id AND CAST(t' + CAST(@i AS NVARCHAR) + '.lvl AS INT) = ' + CAST(@i AS NVARCHAR);
        SET @i += 1;
    END;
END;

-- Assemble and execute the full query
SET @SQL = 'SELECT ' + @SelectColumns + ' FROM tmp.tblSample t1 WHERE CAST(t1.lvl AS INT) = 1 ' + @JoinClauses;
EXEC sp_executesql @SQL;

Output Example (for max level 3): #

Key Notes: #

• We cast lvl to INT because it's stored as NVARCHAR—this ensures numerical comparisons work correctly (avoids issues like '10' being treated as less than '2').
• Using LEFT JOIN ensures ids with fewer levels still appear in results (with NULL in missing level columns). Switch to INNER JOIN if you only want ids with complete hierarchies up to the maximum level.
• The recursive CTE is simpler for path-based output, while dynamic SQL gives you column-based results that match your original query structure.

内容的提问来源于stack exchange，提问作者Ibo