SQL Unpivot语句中按阶段动态计算金额的单语句实现需求

你可以通过在主查询中添加CASE条件判断来实现按阶段动态计算金额，无需辅助表，单条SQL即可完成：

SELECT 
    UnPivotTable."Stage",
    UnPivotTable."Proposals" AS "Stage Date",
    UnPivotTable."Proposal Number",
    CASE UnPivotTable."Stage"
        WHEN 'Stage 1' THEN UnPivotTable."Amount" * 0.1
        WHEN 'Stage 2' THEN UnPivotTable."Amount" * 0.3
        WHEN 'Stage 3' THEN UnPivotTable."Amount" * 0.4
        WHEN 'Stage 4' THEN UnPivotTable."Amount" * 0.2
    END AS "Calculated Amount"
FROM (  
    SELECT
        "Stage 1" AS "Stage 1 Date",
        "Stage 2" AS "Stage 2 Date",
        "Stage 3" AS "Stage 3 Date",
        "Stage 4" AS "Stage 4 Date",
        "Proposal Number",
        "Amount"
    FROM  "Proposals" 
) AS Temp
UNPIVOT (
    "Proposals" FOR "Stage" IN ( 
        Temp."Stage 1 Date", 
        Temp."Stage 2 Date", 
        Temp."Stage 3 Date", 
        Temp."Stage 4 Date" 
    )
) AS UnPivotTable

说明 #

• 放弃使用SELECT UnPivotTable.*，改为显式指定需要的字段，避免引入冗余内容
• 利用CASE语句匹配Stage字段的具体值，分别乘以对应比例系数，直接得到计算后的金额
• 所有逻辑在单条SQL内完成，不需要依赖任何辅助表或额外预处理步骤

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