中文
devexamples

Pivot Rows to Columns CASE Aggregation

Pivot monthly revenue by category using conditional aggregation, generating column lists dynamically for any date range.

SqlData Integrity

Code

snippet
-- 固定月份的手写透视：CASE 进聚合函数
SELECT
  category,
  SUM(amount) FILTER (WHERE month = '2026-07') AS jul_revenue,
  SUM(amount) FILTER (WHERE month = '2026-08') AS aug_revenue,
  SUM(amount) FILTER (WHERE month = '2026-09') AS sep_revenue,
  SUM(amount) AS total_revenue
FROM monthly_revenue
GROUP BY category
ORDER BY total_revenue DESC;

-- 经典写法对照：CASE WHEN 的条件聚合
SELECT
  category,
  SUM(CASE WHEN month = '2026-07' THEN amount ELSE 0 END) AS jul_revenue,
  SUM(CASE WHEN month = '2026-08' THEN amount ELSE 0 END) AS aug_revenue
FROM monthly_revenue
GROUP BY category;

-- 生成动态列清单（应用层据此拼 SQL）
SELECT DISTINCT month
FROM monthly_revenue
WHERE month BETWEEN '2026-07' AND '2026-09'
ORDER BY month;

Related Examples