Transforming Sales Data with SQL PIVOT Operator

Pivot Data Using PIVOT Operator

Write a SQL query to pivot sales data from rows to columns.

Solution:

-- Pivot sales data by year and region.
SELECT *
FROM (
    SELECT Region, Year, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(SalesAmount)
    FOR Year IN ([2021], [2022], [2023])
) AS PivotTable;

Explanation:

1. Purpose of the Query :

1. The goal is to transform sales data from rows (yearly sales per region) into columns (years as separate columns).

2. Key Components :

1. PIVOT Operator : Transforms rows into columns.
2. SUM(SalesAmount) : Aggregates sales data.
3. FOR Year IN : Specifies the columns to create.

3. Why use Pivoting? :

1. Pivoting simplifies reporting by organizing data into a more readable format.

4. Real-World Application :

1. For example, in sales reports, you might use this query to display yearly sales by region in a tabular format.

Additional Notes:

• Ensure the source data is properly structured before pivoting.
• Use this exercise to teach how to transform data for reporting purposes.

For more Practice: Solve these Related Problems:

• Write a SQL query to pivot monthly sales data into quarterly columns for each region.
• Write a SQL query to transform product-wise sales data from rows into columns for better visualization.
• Write a SQL query to pivot inventory data by warehouse and month to show stock levels in a tabular format.
• Write a SQL query to pivot customer feedback scores by year and category for analysis.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise:Create a Recursive Common Table Expression.
Next SQL Exercise: Unpivot Data Using UNPIVOT Operator.