Pivoting Sales Data Dynamically in SQL

Transform Data Using PIVOT with Dynamic Columns

Write a SQL query to dynamically pivot sales data into columns based on unique years.

Solution:

-- Dynamically pivot sales data into columns based on unique years.
DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- Generate a comma-separated list of unique years.
SELECT @Columns = STRING_AGG(QUOTENAME(Year), ',') 
FROM (SELECT DISTINCT Year FROM SalesData) AS Years;

-- Construct the dynamic SQL query.
SET @SQL = '
SELECT *
FROM (
    SELECT Region, Year, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(SalesAmount)
    FOR Year IN (' + @Columns + ')
) AS PivotTable;';

-- Execute the dynamic SQL query.
EXEC sp_executesql @SQL;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to dynamically pivot data when the column names (e.g., years) are not known in advance.

2. Key Components :

1. STRING_AGG : Generates a comma-separated list of unique years.
2. Dynamic SQL : Constructs and executes the pivot query dynamically.
3. PIVOT Operator : Transforms rows into columns.

3. Why use Dynamic Pivoting? :

1. Dynamic pivoting is essential when the number of columns depends on the data itself (e.g., unique years).

4. Real-World Application :

1. For example, in financial reports, you might use this query to display yearly sales data dynamically.

Additional Notes:

• Be cautious with dynamic SQL to avoid SQL injection risks.
• Use this exercise to teach how to handle scenarios where column names are not static.

For more Practice: Solve these Related Problems:

• Write a SQL query to dynamically pivot monthly sales data into columns based on unique months.
• Write a SQL query to pivot inventory levels by warehouse and dynamically adjust column names based on locations.
• Write a SQL query to dynamically pivot survey responses into columns based on question IDs.
• Write a SQL query to pivot financial metrics by year and dynamically include new years as they appear in the dataset.

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

Previous SQL Exercise: Use GROUPING SETS for Multi-Level Aggregation.
Next SQL Exercise: Extracting Nested JSON Objects with JSON_QUERY.