Consolidate Wide Reports — Solution & ExplanationProblems / Consolidate Wide Reports / EditorialApproach

Unpivot each report. Three SELECTs per table — SELECT region, 'jan' AS month, jan_sales AS sales FROM report_team_a, and so on — turn each wide row into three (region, month, sales) rows.

Stack. Because both tables share a shape, the six SELECTs UNION ALL into one long table. No join, no NULL handling: a region only Team A covers simply contributes only Team A rows.

Aggregate. GROUP BY region, month + SUM(sales). Overlapping regions (east, north, south, west) sum across teams; single-team regions (central, coastal) pass through unchanged.

Key InsightThe instinctive approach — FULL OUTER JOIN the two wide tables on region, then add the column pairs — forces coalesce(a.jan_sales, 0) + coalesce(b.jan_sales, 0) for every month and breaks the moment a third team shows up. Unpivot + UNION ALL + GROUP BY treats "how many sources?" as just more rows: consolidation problems are almost always easier in long form.Hints

Unpivot each report with UNION ALL — three SELECTs per table, each emitting a month literal ('jan', 'feb', 'mar') and the matching column as sales.

Both tables have the same shape, so the two long forms stack straight into one set of (region, month, sales) rows — six SELECTs UNION ALLed together.

Finish with GROUP BY region, month + SUM(sales). Regions covered by only one team simply contribute fewer rows — no join and no NULL handling needed.

Solve Consolidate Wide Reports yourself →