如何无需多次自连接拆分父子层级CODE字段为多列？

样本数据 #

DECLARE @Table TABLE
(
    [PKey]  VARCHAR(10)
  , [CKey]  VARCHAR(10)
  , [GCKey] VARCHAR(10)
  , [CODE]  VARCHAR(10)
) ;

INSERT INTO @Table
SELECT 'A','','','A'
UNION ALL
SELECT 'A','AB1','','AAB1'
UNION ALL
SELECT 'A','AB2','','AAB2'
UNION ALL
SELECT 'A','AB2','AB2C1','AAB2C1'
UNION ALL
SELECT 'A','AB3','','AAB3'
UNION ALL
SELECT 'A','AB3','AB3C1','AAB3C1'
UNION ALL
SELECT 'A','AB3','AB3C2','AAB3C2'
UNION ALL
SELECT 'B','','','B'
UNION ALL
SELECT 'B','BB1','','BBB1'
UNION ALL
SELECT 'C','','','C'
UNION ALL
SELECT 'C','CB1','','CCB1'
UNION ALL
SELECT 'C','CB1','CB1C1','CCB1C1'
UNION ALL
SELECT 'D','','','D_N/A'
UNION ALL
SELECT 'E','','','E'
UNION ALL
SELECT 'E','EB0','','EEB01'

源数据 #

执行查询SELECT * FROM @Table;得到以下结果：

PKey    CKey    GCKey   CODE
A                       A
A       AB1             AAB1
A       AB2             AAB2
A       AB2     AB2C1   AAB2C1
A       AB3             AAB3
A       AB3     AB3C1   AAB3C1
A       AB3     AB3C2   AAB3C2
B                       B
B       BB1             BBB1
C                       C
C       CB1             CCB1
C       CB1     CB1C1   CCB1C1
D                       D_N/A
E                       E
E       EB0             EEB01

需求说明 #

数据分为3级层级结构：[PKey]（父键）> [CKey]（子键）> [GCKey]（孙键）。需要将[CODE]字段拆分为[PCode]、[CCode]、[GCCode]三个字段，每个字段对应填充对应层级的CODE值。

我的尝试 #

用两次左自连接的方式实现，但结果不符合预期：

SELECT      [T1].[PKey]
          , [T1].[CKey]
          , [T1].[GCKey]
          , [T1].[CODE] AS [PCode]
          , COALESCE ( [T2].[CODE], '' ) AS [CCode]
          , COALESCE ( [T3].[CODE], '' ) AS [GCCode]
FROM        @Table AS [T1]
LEFT JOIN   @Table AS [T2]
ON          [T2].[PKey] = [T1].[PKey]
            AND [T2].[CKey] = [T1].[CKey]
            AND [T2].[CKey] <> ''
            AND [T2].[GCKey] = ''
LEFT JOIN   @Table AS [T3]
ON          [T3].[PKey] = [T1].[PKey]
            AND [T3].[CKey] = [T1].[CKey]
            AND [T3].[GCKey] <> ''
            AND [T3].[GCKey] = [T1].[GCKey] ;

当前结果（PCode字段不符合预期） #

PKey    CKey    GCKey   PCode   CCode   GCCode
A                       A       
A       AB1             AAB1    AAB1    
A       AB2             AAB2    AAB2    
A       AB2     AB2C1   AAB2C1  AAB2    AAB2C1
A       AB3             AAB3    AAB3    
A       AB3     AB3C1   AAB3C1  AAB3    AAB3C1
A       AB3     AB3C2   AAB3C2  AAB3    AAB3C2
B                       B       
B       BB1             BBB1    BBB1    
C                       C       
C       CB1             CCB1    CCB1    
C       CB1     CB1C1   CCB1C1  CCB1    CCB1C1
D                       D_N/A       
E                       E       
E       EB0             EEB01   EEB01

预期结果 #

PKey    CKey    GCKey   PCode   CCode   GCCode
A                       A       
A       AB1             A       AAB1    
A       AB2             A       AAB2    
A       AB2     AB2C1   A       AAB2    AAB2C1
A       AB3             A       AAB3    
A       AB3     AB3C1   A       AAB3    AAB3C1
A       AB3     AB3C2   A       AAB3    AAB3C2
B                       B       
B       BB1             B       BBB1    
C                       C       
C       CB1             C       CCB1    
C       CB1     CB1C1   C       CCB1    CCB1C1
D                       D_N/A       
E                       E       
E       EB0             E       EEB01   

解决方案：无需自连接的实现方式 #

可以利用窗口函数的分区聚合特性，一次扫描表就能完成需求，不需要自连接：

SELECT 
    PKey,
    CKey,
    GCKey,
    -- 按PKey分区，取该父级对应的CODE（CKey和GCKey都为空的行）
    MAX(CASE WHEN CKey = '' AND GCKey = '' THEN CODE END) OVER (PARTITION BY PKey) AS PCode,
    -- 按PKey+CKey分区，取该子级对应的CODE（GCKey为空的行），父级行则留空
    CASE 
        WHEN CKey = '' THEN ''
        ELSE MAX(CASE WHEN GCKey = '' THEN CODE END) OVER (PARTITION BY PKey, CKey) 
    END AS CCode,
    -- 孙级行取当前CODE，否则留空
    CASE WHEN GCKey <> '' THEN CODE ELSE '' END AS GCCode
FROM @Table
ORDER BY PKey, CKey, GCKey;

逻辑说明 #

1. PCode：通过PARTITION BY PKey将同一父键的行分组，用MAX()聚合取出该分组中CKey和GCKey都为空的行的CODE值，也就是父级本身的CODE。
2. CCode：通过PARTITION BY PKey, CKey将同一父键+子键的行分组，取出该分组中GCKey为空的行的CODE值；如果是父级行（CKey为空）则直接留空。
3. GCCode：直接判断当前行是否为孙级（GCKey不为空），是则取当前CODE，否则留空。

这个方法只需要扫描一次表，性能比多次自连接更优，同时逻辑清晰易维护。

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