适配可变层级结构的层级数据透视SQL查询方案需求

首先，咱们先拆解下你遇到的问题：你的递归CTE之所以返回全NULL，核心问题是递归过程中没有保留之前层级已经获取到的组织类型值——每次递归只给当前匹配的类型赋值，其他列直接设为NULL，最后取最大FLAG的时候，自然只有最后一层的类型有值，其余全是空。另外还能看到一些拼写小错误（比如PROEPRTY应该是PROPERTY，CASE里的COUNTY写成了COUNTRY），这些也会导致列值异常。

下面是修正后的完整查询，核心思路是递归时继承上一层已有的非空值，只在当前层级匹配到对应组织类型时更新列值：

;WITH ALLORGS AS (
    -- 整合组织、层级、类型数据，用LEFT JOIN更贴合业务逻辑（Cost Center必有上级）
    SELECT 
        ORGS.ID, 
        ORGS.ORG_NAME, 
        HIER.ID_PARENTORG, 
        TYP.ORG_TYPE_DESCR
    FROM ORGANIATIONS AS ORGS
    LEFT JOIN HIERARCHYTABLE AS HIER 
        ON ORGS.ID = HIER.ID_ORG
    LEFT JOIN ORGANIZATION_TYPES AS TYP 
        ON ORGS.ID_ORG_TYPE = TYP.ID
),
CTE AS (
    -- 锚点：从最低层级（Cost Center）开始，初始化所有上层列为空
    SELECT 
        ID,
        ID_PARENTORG,
        ORG_NAME AS COSTCNTR,
        CAST('' AS VARCHAR(100)) AS UNIT,
        CAST('' AS VARCHAR(100)) AS REGION,
        CAST('' AS VARCHAR(100)) AS DDA_POOL,
        CAST('' AS VARCHAR(100)) AS COUNTY,
        CAST('' AS VARCHAR(100)) AS STATE,
        CAST('' AS VARCHAR(100)) AS BUSINESS_UNIT,
        CAST('' AS VARCHAR(100)) AS PROPERTY, -- 修正拼写错误
        CAST('' AS VARCHAR(100)) AS DISTRICT,
        1 AS FLAG
    FROM ALLORGS
    WHERE ORG_TYPE_DESCR = 'COST CENTER'

    UNION ALL

    -- 递归逻辑：继承上一层的非空值，仅更新当前层级匹配的类型列
    SELECT 
        T1.ID,
        L2.ID_PARENTORG,
        T1.COSTCNTR, -- 始终保留Cost Center名称
        -- 若当前上级是UNIT则用其名称，否则继承上一层的UNIT值
        CASE WHEN L2.ORG_TYPE_DESCR = 'UNIT' THEN L2.ORG_NAME ELSE T1.UNIT END AS UNIT,
        CASE WHEN L2.ORG_TYPE_DESCR = 'REGION' THEN L2.ORG_NAME ELSE T1.REGION END AS REGION,
        CASE WHEN L2.ORG_TYPE_DESCR = 'DDA_POOL' THEN L2.ORG_NAME ELSE T1.DDA_POOL END AS DDA_POOL,
        CASE WHEN L2.ORG_TYPE_DESCR = 'COUNTY' THEN L2.ORG_NAME ELSE T1.COUNTY END AS COUNTY, -- 修正类型名称拼写
        CASE WHEN L2.ORG_TYPE_DESCR = 'STATE' THEN L2.ORG_NAME ELSE T1.STATE END AS STATE,
        CASE WHEN L2.ORG_TYPE_DESCR = 'BUSINESS_UNIT' THEN L2.ORG_NAME ELSE T1.BUSINESS_UNIT END AS BUSINESS_UNIT,
        CASE WHEN L2.ORG_TYPE_DESCR = 'PROPERTY' THEN L2.ORG_NAME ELSE T1.PROPERTY END AS PROPERTY,
        CASE WHEN L2.ORG_TYPE_DESCR = 'DISTRICT' THEN L2.ORG_NAME ELSE T1.DISTRICT END AS DISTRICT,
        T1.FLAG + 1 AS FLAG
    FROM CTE AS T1
    INNER JOIN ALLORGS AS L2 
        ON T1.ID_PARENTORG = L2.ID
    -- 终止条件：当没有上级时停止递归
    WHERE L2.ID_PARENTORG IS NOT NULL OR L2.ID IS NOT NULL
)
-- 取每个Cost Center的最深递归层级（确保所有上级都已遍历）
SELECT 
    a.ID,
    a.COSTCNTR,
    a.UNIT,
    a.REGION,
    a.DDA_POOL,
    a.COUNTY,
    a.STATE,
    a.BUSINESS_UNIT,
    a.PROPERTY,
    a.DISTRICT
FROM CTE AS a
INNER JOIN (
    SELECT ID, MAX(FLAG) AS MAX_FLAG
    FROM CTE
    GROUP BY ID
) AS b 
    ON a.ID = b.ID AND a.FLAG = b.MAX_FLAG

关键改进点说明： #

1. 递归时保留已有值：每个CASE语句不再返回NULL，而是继承上一层的对应列值，这样每遍历一个上级，只会更新匹配的类型列，其他列保持之前找到的值。
2. 修正拼写错误：修复了PROEPRTY和COUNTY的拼写问题，避免因字段/类型名称不匹配导致的空值。
3. 优化关联逻辑：把FULL OUTER JOIN改成LEFT JOIN，更符合业务场景（Cost Center一定属于某个上层组织，不需要保留无关联的层级/类型数据）。
4. 明确终止条件：递归时判断上级是否存在，避免不必要的循环。

适配层级变化的扩展性 #

如果后续业务新增了上层组织类型（比如在Country之上加Hemisphere），只需要在CTE的锚点和递归部分新增对应的列和CASE语句即可，不需要修改整体递归逻辑，完全适配层级结构的变更。

如果需要完全动态的列（比如组织类型随时新增，不想手动修改SQL），可以基于这个逻辑写动态SQL，通过查询ORGANIZATION_TYPES表自动生成列和CASE语句，但静态SQL在稳定性和可维护性上更优，适合组织类型相对固定但层级可变的场景。

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