w3resource

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.

Go to:


PREV : Create a Recursive Common Table Expression.
NEXT : Unpivot Data Using UNPIVOT Operator.



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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.