如何将员工层级数据扁平化为列？SQL场景实现问询

Hey there! Let's break down how to flatten your hierarchical employee data into the column-based structure you need. This is a common problem in SQL, and I'll walk you through step-by-step solutions that work for most major databases.

Core Idea #

The key steps are:

• Use a recursive CTE to traverse each employee's full chain of managers (from direct manager up to the top-level leader).
• Split that chain into individual rows, each tagged with a level number (Level 1 = direct manager, Level 2 = manager's manager, etc.).
• Pivot those rows into columns, filling in NULL for any levels that don't exist for an employee.

Step 1: Capture Full Hierarchy with Recursive CTE #

First, we'll build a recursive CTE to generate a complete chain of managers for every employee. This will give us a comma-separated string (or array, depending on your database) of manager IDs, plus a count of how many levels deep each employee's hierarchy goes.

Example for SQL Server: #

WITH EmployeeHierarchy AS (
    -- Base case: All employees, start with their direct manager
    SELECT 
        EmployeeID,
        ManagerID,
        CAST(CASE WHEN ManagerID IS NOT NULL THEN CAST(ManagerID AS VARCHAR(MAX)) ELSE '' END AS VARCHAR(MAX)) AS ManagerChain,
        1 AS CurrentLevel
    FROM employees
    UNION ALL
    -- Recursive case: Traverse up to each manager's manager
    SELECT 
        eh.EmployeeID,
        e.ManagerID,
        CONCAT(eh.ManagerChain, ',', CAST(e.ManagerID AS VARCHAR(MAX))),
        eh.CurrentLevel + 1
    FROM EmployeeHierarchy eh
    INNER JOIN employees e ON eh.ManagerID = e.EmployeeID
    WHERE e.ManagerID IS NOT NULL -- Stop when we reach the top-level leader
)
SELECT * FROM EmployeeHierarchy;

This outputs each employee's ID, their current manager, a chain of all managers above them, and the current level in the hierarchy.

Step 2: Split & Pivot to Columns #

Next, we split the manager chain into individual rows, then pivot those rows into the flat columns you need. We'll also ensure that employees with shorter hierarchies get NULL for higher-level manager columns.

SQL Server Full Solution (Fixed 5 Levels): #

WITH EmployeeHierarchy AS (
    SELECT 
        EmployeeID,
        ManagerID,
        CAST(CASE WHEN ManagerID IS NOT NULL THEN CAST(ManagerID AS VARCHAR(MAX)) ELSE '' END AS VARCHAR(MAX)) AS ManagerChain
    FROM employees
    UNION ALL
    SELECT 
        eh.EmployeeID,
        e.ManagerID,
        CONCAT(eh.ManagerChain, ',', CAST(e.ManagerID AS VARCHAR(MAX)))
    FROM EmployeeHierarchy eh
    INNER JOIN employees e ON eh.ManagerID = e.EmployeeID
    WHERE e.ManagerID IS NOT NULL
),
SplitChains AS (
    -- Split the manager chain into individual rows with level numbers
    SELECT 
        eh.EmployeeID,
        CAST(value AS INT) AS ManagerID,
        ROW_NUMBER() OVER (PARTITION BY eh.EmployeeID ORDER BY (SELECT NULL)) AS LevelNumber
    FROM EmployeeHierarchy eh
    CROSS APPLY STRING_SPLIT(eh.ManagerChain, ',')
    WHERE eh.ManagerChain <> ''
)
-- Pivot rows into columns
SELECT 
    emp.EmployeeID,
    COALESCE(MAX(CASE WHEN sc.LevelNumber = 1 THEN sc.ManagerID END), NULL) AS ManagerID,
    COALESCE(MAX(CASE WHEN sc.LevelNumber = 2 THEN sc.ManagerID END), NULL) AS Level2ManagerID,
    COALESCE(MAX(CASE WHEN sc.LevelNumber = 3 THEN sc.ManagerID END), NULL) AS Level3ManagerID,
    COALESCE(MAX(CASE WHEN sc.LevelNumber = 4 THEN sc.ManagerID END), NULL) AS Level4ManagerID,
    COALESCE(MAX(CASE WHEN sc.LevelNumber = 5 THEN sc.ManagerID END), NULL) AS Level5ManagerID
FROM employees emp
LEFT JOIN SplitChains sc ON emp.EmployeeID = sc.EmployeeID
GROUP BY emp.EmployeeID
ORDER BY emp.EmployeeID;

How This Works: #

• SplitChains breaks the comma-separated manager chain into individual rows, assigning each manager a level number (1 = direct manager, 2 = next level up, etc.).
• The final SELECT uses CASE statements with MAX to pivot these rows into columns. COALESCE ensures that any missing levels show up as NULL.

Step 3: Handle Dynamic Hierarchy Levels #

If your organization's hierarchy depth varies (and you don't want to hardcode 5 levels), use dynamic SQL to automatically generate columns for every level present in the data.

SQL Server Dynamic SQL Example: #

DECLARE @MaxLevel INT;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @ColumnList NVARCHAR(MAX);

-- Get the maximum hierarchy level in the organization
SELECT @MaxLevel = COALESCE(MAX(LevelNumber), 0) FROM (
    WITH EmployeeHierarchy AS (
        SELECT 
            EmployeeID,
            CAST(CASE WHEN ManagerID IS NOT NULL THEN CAST(ManagerID AS VARCHAR(MAX)) ELSE '' END AS VARCHAR(MAX)) AS ManagerChain
        FROM employees
        UNION ALL
        SELECT 
            eh.EmployeeID,
            CONCAT(eh.ManagerChain, ',', CAST(e.ManagerID AS VARCHAR(MAX)))
        FROM EmployeeHierarchy eh
        JOIN employees e ON eh.ManagerID = e.EmployeeID
        WHERE e.ManagerID IS NOT NULL
    )
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY eh.EmployeeID ORDER BY (SELECT NULL)) AS LevelNumber
    FROM EmployeeHierarchy eh
    CROSS APPLY STRING_SPLIT(eh.ManagerChain, ',')
    WHERE eh.ManagerChain <> ''
) AS Levels;

-- Generate column list (ManagerID, Level2ManagerID, ..., LevelNManagerID)
SET @ColumnList = 'COALESCE(MAX(CASE WHEN sc.LevelNumber = 1 THEN sc.ManagerID END), NULL) AS ManagerID';
IF @MaxLevel >= 2
BEGIN
    SET @ColumnList = @ColumnList + ',' + STRING_AGG(
        CONCAT('COALESCE(MAX(CASE WHEN sc.LevelNumber = ', n, ' THEN sc.ManagerID END), NULL) AS Level', n, 'ManagerID'),
        ','
    ) FROM GENERATE_SERIES(2, @MaxLevel) n;
END
-- Add NULL columns if max level is less than 5 (per your example)
IF @MaxLevel < 5
BEGIN
    SET @ColumnList = @ColumnList + ',' + STRING_AGG(
        CONCAT('NULL AS Level', n, 'ManagerID'),
        ','
    ) FROM GENERATE_SERIES(@MaxLevel + 1, 5) n;
END

-- Build and execute the dynamic SQL query
SET @SQL = N'
WITH EmployeeHierarchy AS (
    SELECT 
        EmployeeID,
        CAST(CASE WHEN ManagerID IS NOT NULL THEN CAST(ManagerID AS VARCHAR(MAX)) ELSE '''' END AS VARCHAR(MAX)) AS ManagerChain
    FROM employees
    UNION ALL
    SELECT 
        eh.EmployeeID,
        CONCAT(eh.ManagerChain, '', '', CAST(e.ManagerID AS VARCHAR(MAX)))
    FROM EmployeeHierarchy eh
    JOIN employees e ON eh.ManagerID = e.EmployeeID
    WHERE e.ManagerID IS NOT NULL
),
SplitChains AS (
    SELECT 
        eh.EmployeeID,
        CAST(value AS INT) AS ManagerID,
        ROW_NUMBER() OVER (PARTITION BY eh.EmployeeID ORDER BY (SELECT NULL)) AS LevelNumber
    FROM EmployeeHierarchy eh
    CROSS APPLY STRING_SPLIT(eh.ManagerChain, '','')
    WHERE eh.ManagerChain <> ''''
)
SELECT 
    emp.EmployeeID,
    ' + @ColumnList + '
FROM employees emp
LEFT JOIN SplitChains sc ON emp.EmployeeID = sc.EmployeeID
GROUP BY emp.EmployeeID
ORDER BY emp.EmployeeID;';

EXEC sp_executesql @SQL;

Adaptations for Other Databases #

PostgreSQL: #

Use arrays instead of strings for the manager chain, and unnest to split them:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT 
        EmployeeID,
        ManagerID,
        ARRAY[ManagerID] AS ManagerChain
    FROM employees
    WHERE ManagerID IS NOT NULL
    UNION ALL
    SELECT 
        eh.EmployeeID,
        e.ManagerID,
        eh.ManagerChain || e.ManagerID
    FROM EmployeeHierarchy eh
    JOIN employees e ON eh.ManagerID = e.EmployeeID
    WHERE e.ManagerID IS NOT NULL
),
SplitChains AS (
    SELECT 
        eh.EmployeeID,
        unnest(eh.ManagerChain) AS ManagerID,
        generate_subscripts(eh.ManagerChain, 1) AS LevelNumber
    FROM EmployeeHierarchy eh
    UNION ALL
    -- Handle top-level employees with no manager
    SELECT EmployeeID, NULL, NULL FROM employees WHERE ManagerID IS NULL
)
SELECT 
    emp.EmployeeID,
    MAX(CASE WHEN sc.LevelNumber = 1 THEN sc.ManagerID END) AS ManagerID,
    MAX(CASE WHEN sc.LevelNumber = 2 THEN sc.ManagerID END) AS Level2ManagerID,
    MAX(CASE WHEN sc.LevelNumber = 3 THEN sc.ManagerID END) AS Level3ManagerID,
    MAX(CASE WHEN sc.LevelNumber = 4 THEN sc.ManagerID END) AS Level4ManagerID,
    MAX(CASE WHEN sc.LevelNumber = 5 THEN sc.ManagerID END) AS Level5ManagerID
FROM employees emp
LEFT JOIN SplitChains sc ON emp.EmployeeID = sc.EmployeeID
GROUP BY emp.EmployeeID
ORDER BY emp.EmployeeID;

MySQL: #

Use SUBSTRING_INDEX to split the manager chain (note: this works best for fixed levels):

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT 
        EmployeeID,
        ManagerID,
        CAST(ManagerID AS CHAR(255)) AS ManagerChain,
        1 AS CurrentLevel
    FROM employees
    WHERE ManagerID IS NOT NULL
    UNION ALL
    SELECT 
        eh.EmployeeID,
        e.ManagerID,
        CONCAT(eh.ManagerChain, ',', e.ManagerID),
        eh.CurrentLevel + 1
    FROM EmployeeHierarchy eh
    JOIN employees e ON eh.ManagerID = e.EmployeeID
    WHERE e.ManagerID IS NOT NULL
)
SELECT 
    emp.EmployeeID,
    -- Direct manager (Level 1)
    CASE WHEN eh.CurrentLevel >=1 THEN eh.ManagerID ELSE NULL END AS ManagerID,
    -- Level 2 manager
    CASE WHEN eh.CurrentLevel >=2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(eh.ManagerChain, ',', 2), ',', -1) ELSE NULL END AS Level2ManagerID,
    -- Level 3 manager
    CASE WHEN eh.CurrentLevel >=3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(eh.ManagerChain, ',', 3), ',', -1) ELSE NULL END AS Level3ManagerID,
    -- Level 4 manager
    CASE WHEN eh.CurrentLevel >=4 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(eh.ManagerChain, ',', 4), ',', -1) ELSE NULL END AS Level4ManagerID,
    -- Level 5 manager (NULL if no such level)
    CASE WHEN eh.CurrentLevel >=5 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(eh.ManagerChain, ',', 5), ',', -1) ELSE NULL END AS Level5ManagerID
FROM employees emp
LEFT JOIN EmployeeHierarchy eh ON emp.EmployeeID = eh.EmployeeID
GROUP BY emp.EmployeeID
ORDER BY emp.EmployeeID;

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