PostgreSQL按收据类型将单列金额拆分为三列

现有表包含Receipt No（收据编号）和Amount（金额）字段，Receipt No分为Bank、Refund、Discount三种类型，可通过以下SQL语句将不同类型的金额拆分至独立列：

基础列转行（逐行展示） #

方法1：使用CASE WHEN #

SELECT
    "Receipt No",
    CASE WHEN "Receipt No" LIKE '%Bank%' THEN Amount ELSE 0 END AS "Bank Amount",
    CASE WHEN "Receipt No" LIKE '%Refund%' THEN Amount ELSE 0 END AS "Refund Amount",
    CASE WHEN "Receipt No" LIKE '%Discount%' THEN Amount ELSE 0 END AS "Discount Amount"
FROM receipts;

方法2：使用PostgreSQL专属FILTER子句（更简洁） #

SELECT
    "Receipt No",
    Amount FILTER (WHERE "Receipt No" LIKE '%Bank%') AS "Bank Amount",
    Amount FILTER (WHERE "Receipt No" LIKE '%Refund%') AS "Refund Amount",
    Amount FILTER (WHERE "Receipt No" LIKE '%Discount%') AS "Discount Amount"
FROM receipts;

按收据编号汇总金额 #

如果需要对同一收据编号下的不同类型金额进行汇总，可添加聚合函数：

SELECT
    "Receipt No",
    SUM(Amount) FILTER (WHERE "Receipt No" LIKE '%Bank%') AS "Total Bank Amount",
    SUM(Amount) FILTER (WHERE "Receipt No" LIKE '%Refund%') AS "Total Refund Amount",
    SUM(Amount) FILTER (WHERE "Receipt No" LIKE '%Discount%') AS "Total Discount Amount"
FROM receipts
GROUP BY "Receipt No";

注意事项 #

• 上述语句假设Receipt No中包含类型关键词（如"Bank"），若类型匹配规则不同，直接调整WHERE后的条件即可。
• 若希望不匹配类型的列显示NULL而非0，去掉CASE WHEN中的ELSE 0部分，FILTER子句默认返回NULL。

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