Apprendre Pivot-Style Reports with CASE WHEN

Section 1. Chapitre 9

single

Glissez pour afficher le menu

Pivot-style reporting is a powerful technique in SQL that allows you to transform row-based data into a summary table where categories become columns. This approach is particularly useful for producing cross-tabulations—tables that display the distribution of values across two or more variables. In SQL, you can achieve this by combining aggregation functions such as SUM or COUNT with CASE WHEN expressions. By selectively aggregating values based on conditions, you create new columns for each category or time period, making it easy to compare data side by side.

Definition

A cross-tabulation (or crosstab) is a summary table that displays the frequency or amount of data for combinations of two or more categorical variables. In business, cross-tabs are widely used to analyze sales by product and region, employee counts by department and job title, or customer counts by segment and month. They help decision-makers spot trends, outliers, and patterns that are not immediately obvious in unpivoted, row-based tables.


              12345678910
            
SELECT
  product_name,
  SUM(CASE WHEN DATE_PART('quarter', sale_date) = 1 THEN total_sales ELSE 0 END) AS q1_sales,
  SUM(CASE WHEN DATE_PART('quarter', sale_date) = 2 THEN total_sales ELSE 0 END) AS q2_sales,
  SUM(CASE WHEN DATE_PART('quarter', sale_date) = 3 THEN total_sales ELSE 0 END) AS q3_sales,
  SUM(CASE WHEN DATE_PART('quarter', sale_date) = 4 THEN total_sales ELSE 0 END) AS q4_sales
FROM product_sales
WHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'
GROUP BY product_name
ORDER BY product_name;

Suppose the finance team requests a table that shows monthly sales for each product, with months as columns. You can use the same CASE WHEN logic to pivot the data by month, allowing for quick visual comparisons across products and time periods.


              123456789101112
            
SELECT
  product_name,
  SUM(CASE WHEN DATE_PART('month', sale_date) = 1 THEN total_sales ELSE 0 END) AS jan_sales,
  SUM(CASE WHEN DATE_PART('month', sale_date) = 2 THEN total_sales ELSE 0 END) AS feb_sales,
  SUM(CASE WHEN DATE_PART('month', sale_date) = 3 THEN total_sales ELSE 0 END) AS mar_sales,
  SUM(CASE WHEN DATE_PART('month', sale_date) = 4 THEN total_sales ELSE 0 END) AS apr_sales,
  SUM(CASE WHEN DATE_PART('month', sale_date) = 5 THEN total_sales ELSE 0 END) AS may_sales,
  SUM(CASE WHEN DATE_PART('month', sale_date) = 6 THEN total_sales ELSE 0 END) AS jun_sales
FROM product_sales
WHERE sale_date >= '2024-01-01' AND sale_date < '2024-07-01'
GROUP BY product_name
ORDER BY product_name;

This approach can be adapted to a variety of reporting scenarios, enabling you to create highly flexible analytical summaries.

Tâche

Glissez pour commencer à coder

Create a pivot-style summary table showing the number of employees in each department by job title.

For each department, count the number of employees with the position 'Manager'.
Count the number of employees with the position 'Sales Representative'.
Count the number of employees with the position 'Software Engineer'.
Count the number of employees with the position 'HR Specialist'.
Each count should appear as a separate column, with departments as rows.

Solution

Passez à un bureau pour une pratique réelleContinuez d'où vous êtes en utilisant l'une des options ci-dessous

Tout était clair ?

Merci pour vos commentaires !

Section 1. Chapitre 9

single

Demandez à l'IA

Posez n'importe quelle question ou essayez l'une des questions suggérées pour commencer notre discussion