SQL Server从tbTest表查询并按规则填充d1-d5列的实现问题

现有名称为tbTest的数据表，表结构及示例数据如下：

id_main|operation|id_cli|name  |dueDate   |value|debtShare|address      |id_parcel|d1        |d2        |d3        |d4        |d5|Type|
    253|       66|  9876|Johnny|2018-11-01|  1.2|        1|abc street 12|     7197|2018-11-01|          |          |          |  |N   |
    253|       67|  9876|Johnny|2018-11-01|  3.7|        4|abc street 12|     7198|2018-11-01|          |          |          |  |N   |
    254|       68|  9876|Johnny|2015-11-20|  9.3|        1|abc street 12|     4670|2015-11-20|2016-09-20|2017-11-20|2020-12-22|  |Y   |
    254|       68|  5432|David |2015-11-20|  9.3|        2|axe avenue 46|     4670|2015-11-20|2016-09-20|2017-11-20|2020-12-22|  |Y   |

• 当Type=N时：仅将当前行的dueDate填入d1列，其余d列留空，保留完整行数据
• 当Type=Y时：
  • 按id_main、id_cli分组，将组内所有dueDate按升序排序，依次填入d1、d2、d3、d4、d5列
  • 每个分组仅保留id_parcel最小的一行数据

WITH RankedData AS (
    -- 给Type=Y的分组分别标记id_parcel排序、dueDate排序
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY id_main, id_cli ORDER BY id_parcel ASC) AS parcel_rn,
        ROW_NUMBER() OVER (PARTITION BY id_main, id_cli ORDER BY dueDate ASC) AS date_rn
    FROM tbTest
),
PivotedDates AS (
    -- 分组转置日期到d1-d5列
    SELECT 
        id_main,
        id_cli,
        MAX(CASE WHEN date_rn = 1 THEN dueDate END) AS d1,
        MAX(CASE WHEN date_rn = 2 THEN dueDate END) AS d2,
        MAX(CASE WHEN date_rn = 3 THEN dueDate END) AS d3,
        MAX(CASE WHEN date_rn = 4 THEN dueDate END) AS d4,
        MAX(CASE WHEN date_rn = 5 THEN dueDate END) AS d5
    FROM RankedData
    WHERE Type = 'Y'
    GROUP BY id_main, id_cli
)
-- 合并两类数据输出
SELECT 
    t.id_main,
    t.operation,
    t.id_cli,
    t.name,
    t.dueDate,
    t.value,
    t.debtShare,
    t.address,
    t.id_parcel,
    CASE WHEN t.Type = 'N' THEN t.dueDate ELSE p.d1 END AS d1,
    CASE WHEN t.Type = 'N' THEN NULL ELSE p.d2 END AS d2,
    CASE WHEN t.Type = 'N' THEN NULL ELSE p.d3 END AS d3,
    CASE WHEN t.Type = 'N' THEN NULL ELSE p.d4 END AS d4,
    CASE WHEN t.Type = 'N' THEN NULL ELSE p.d5 END AS d5,
    t.Type
FROM tbTest t
LEFT JOIN PivotedDates p ON t.id_main = p.id_main AND t.id_cli = p.id_cli
WHERE 
    t.Type = 'N'
    OR (t.Type = 'Y' AND EXISTS (
        SELECT 1 FROM RankedData r 
        WHERE r.id_main = t.id_main 
        AND r.id_cli = t.id_cli 
        AND r.id_parcel = t.id_parcel 
        AND r.parcel_rn = 1
    ))
ORDER BY t.id_main, t.operation, t.id_cli

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