SQL Server中按层级关联行数据生成指定结构结果的需求

输入表（表名：SourceTable） #

期望结果表 #

具体要求 #

• 排除Code为0或Description为"Total"的行
• 将Code长度大于3的行作为父级，关联其后续的Code在1到N（如1、2等）的子行
• 新增Property列，存储子行的Description
• 保留子行的July和August数值

解决方案 #

可以通过窗口函数分组标记父级范围，再关联父行与子行实现需求：

WITH GroupedData AS (
    SELECT 
        Id,
        Code,
        Description,
        July,
        August,
        -- 累计父级行数量生成组ID，同一父级及其子行属于同一组
        SUM(CASE WHEN LEN(ISNULL(Code, '')) > 3 THEN 1 ELSE 0 END) OVER (ORDER BY Id) AS ParentGroupId
    FROM SourceTable
    -- 提前过滤不需要的行
    WHERE Code != 0 AND Description != 'Total'
),
ParentRows AS (
    SELECT 
        ParentGroupId,
        Code AS ParentCode,
        Description AS ParentDescription
    FROM GroupedData
    WHERE LEN(ISNULL(Code, '')) > 3
)
SELECT 
    pr.ParentCode AS Code,
    pr.ParentDescription AS Description,
    gd.Description AS Property,
    gd.July,
    gd.August
FROM GroupedData gd
JOIN ParentRows pr ON gd.ParentGroupId = pr.ParentGroupId
-- 仅保留子行（非父级行）
WHERE LEN(ISNULL(gd.Code, '')) <= 3
ORDER BY pr.ParentCode, gd.Id;

逻辑说明 #

1. GroupedData：先过滤掉不符合要求的行，再用SUM() OVER (ORDER BY Id)给每个父级行及其后续子行分配相同的组ID，确保父行和对应的子行归属同一组。
2. ParentRows：提取所有父级行的组ID、Code和Description，作为关联的基准。
3. 最终关联查询：将父行的信息和子行的月份、Description（作为Property）整合，得到目标结果。

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