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:
- The goal is to demonstrate how to dynamically pivot data when the column names (e.g., years) are not known in advance.
- STRING_AGG : Generates a comma-separated list of unique years.
- Dynamic SQL : Constructs and executes the pivot query dynamically.
- PIVOT Operator : Transforms rows into columns.
- Dynamic pivoting is essential when the number of columns depends on the data itself (e.g., unique years).
- For example, in financial reports, you might use this query to display yearly sales data dynamically.
1. Purpose of the Query :
2. Key Components :
3. Why use Dynamic Pivoting? :
4. Real-World Application :
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.
Go to:
PREV : Use GROUPING SETS for Multi-Level Aggregation.
NEXT : Extracting Nested JSON Objects with JSON_QUERY.
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.