如何用Pivot实现多列分组的项目拨款间隔数据展示？

核心思路 #

先通过窗口函数给每个项目的拨款记录按CreateDate排序分配间隔编号（仅保留前3条），再用条件聚合实现多字段的横向转置（比PIVOT更适配多字段场景）。

假设表结构（基于需求推导，若实际结构有差异可调整关联逻辑） #

• Projects：ProjectID, ProjectName（项目基础信息表）
• ProviderRequests：RequestID, ProjectID, ProviderRequestDate, ProviderRequestAmount（服务商请求信息表）
• ProjectDisbursement：DisbursementID, ProjectID, CommissionResponseDate, CommissionResponse, DisbursementDate, DisbursementAmount, CreateDate（拨款记录表）

完整SQL脚本 #

WITH RankedDisbursements AS (
    -- 关联三张表，给每个项目的拨款记录按CreateDate排序分配间隔编号
    SELECT
        p.ProjectName,
        pr.ProviderRequestDate,
        pr.ProviderRequestAmount,
        pd.CommissionResponseDate,
        pd.CommissionResponse,
        pd.DisbursementDate,
        pd.DisbursementAmount,
        -- 按项目分区，CreateDate升序排序，生成1-3的间隔编号
        ROW_NUMBER() OVER (PARTITION BY p.ProjectName ORDER BY pd.CreateDate ASC) AS IntervalNo
    FROM
        Projects p
    JOIN ProviderRequests pr ON p.ProjectID = pr.ProjectID
    JOIN ProjectDisbursement pd ON p.ProjectID = pd.ProjectID
)
-- 条件聚合转置为横向结构，保留最多3个间隔
SELECT
    ProjectName,
    -- 间隔1的6个字段
    MAX(CASE WHEN IntervalNo = 1 THEN ProviderRequestDate END) AS Interval1_ProviderRequestDate,
    MAX(CASE WHEN IntervalNo = 1 THEN ProviderRequestAmount END) AS Interval1_ProviderRequestAmount,
    MAX(CASE WHEN IntervalNo = 1 THEN CommissionResponseDate END) AS Interval1_CommissionResponseDate,
    MAX(CASE WHEN IntervalNo = 1 THEN CommissionResponse END) AS Interval1_CommissionResponse,
    MAX(CASE WHEN IntervalNo = 1 THEN DisbursementDate END) AS Interval1_DisbursementDate,
    MAX(CASE WHEN IntervalNo = 1 THEN DisbursementAmount END) AS Interval1_DisbursementAmount,
    -- 间隔2的6个字段
    MAX(CASE WHEN IntervalNo = 2 THEN ProviderRequestDate END) AS Interval2_ProviderRequestDate,
    MAX(CASE WHEN IntervalNo = 2 THEN ProviderRequestAmount END) AS Interval2_ProviderRequestAmount,
    MAX(CASE WHEN IntervalNo = 2 THEN CommissionResponseDate END) AS Interval2_CommissionResponseDate,
    MAX(CASE WHEN IntervalNo = 2 THEN CommissionResponse END) AS Interval2_CommissionResponse,
    MAX(CASE WHEN IntervalNo = 2 THEN DisbursementDate END) AS Interval2_DisbursementDate,
    MAX(CASE WHEN IntervalNo = 2 THEN DisbursementAmount END) AS Interval2_DisbursementAmount,
    -- 间隔3的6个字段
    MAX(CASE WHEN IntervalNo = 3 THEN ProviderRequestDate END) AS Interval3_ProviderRequestDate,
    MAX(CASE WHEN IntervalNo = 3 THEN ProviderRequestAmount END) AS Interval3_ProviderRequestAmount,
    MAX(CASE WHEN IntervalNo = 3 THEN CommissionResponseDate END) AS Interval3_CommissionResponseDate,
    MAX(CASE WHEN IntervalNo = 3 THEN CommissionResponse END) AS Interval3_CommissionResponse,
    MAX(CASE WHEN IntervalNo = 3 THEN DisbursementDate END) AS Interval3_DisbursementDate,
    MAX(CASE WHEN IntervalNo = 3 THEN DisbursementAmount END) AS Interval3_DisbursementAmount
FROM
    RankedDisbursements
WHERE
    IntervalNo <= 3 -- 仅保留最多3个间隔
GROUP BY
    ProjectName
ORDER BY
    ProjectName;

关键说明 #

1. 窗口函数分区排序：ROW_NUMBER() OVER (PARTITION BY p.ProjectName ORDER BY pd.CreateDate ASC) 确保每个项目的拨款记录按创建时间顺序分配1、2、3的间隔编号，超过3条的会被后续WHERE条件过滤。
2. 条件聚合转置：用CASE配合MAX（或MIN，因为每个间隔编号对应唯一一条记录）将纵向的多条记录转成横向的多列组，每个间隔对应6个字段。
3. 适配实际表结构：如果你的表关联逻辑（比如外键字段名）、字段类型有差异，只需调整CTE中的关联条件和字段引用即可。

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