SQL Exercise – 11

SQL Exercise:

Given a table Sales that records sales data for various products across multiple months, write a query to dynamically pivot the data such that each month appears as a column. The number of months is not fixed, so the solution must handle a dynamic list of months.

Create table script:

CREATE TABLE Sales (
Product VARCHAR(50),
Month VARCHAR(20),
SalesAmount DECIMAL(10, 2)
);

Insert sample data:

INSERT INTO Sales (Product, Month, SalesAmount)
VALUES
('ProductA', 'January', 5000.00),
('ProductA', 'February', 7000.00),
('ProductA', 'March', 8000.00),
('ProductB', 'January', 4000.00),
('ProductB', 'February', 3000.00),
('ProductB', 'March', 6000.00),
('ProductC', 'January', 9000.00),
('ProductC', 'February', 5000.00),
('ProductC', 'March', 7000.00);

Solution:

DECLARE @columns NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

-- Step 1: Create a temp table for month order
CREATE TABLE #MonthOrder (
MonthName VARCHAR(20),
MonthOrder INT
);

-- Insert predefined month names and their order
INSERT INTO #MonthOrder (MonthName, MonthOrder)
VALUES 
('January', 1), ('February', 2), ('March', 3),
('April', 4), ('May', 5), ('June', 6),
('July', 7), ('August', 8), ('September', 9),
('October', 10), ('November', 11), ('December', 12);

-- Step 2: Generate the dynamic list of months in the correct order
SELECT 
@columns = STRING_AGG(QUOTENAME(MonthName), ',') WITHIN GROUP (ORDER BY MonthOrder ASC)
FROM 
(SELECT DISTINCT m.MonthName, m.MonthOrder
FROM Sales s
JOIN #MonthOrder m ON s.Month = m.MonthName
) AS OrderedMonths;

-- Step 3: Construct the dynamic SQL query
SET @query = '
SELECT 
Product, ' + @columns + '
FROM 
(SELECT Product, Month, SalesAmount FROM Sales) AS SourceTable
PIVOT 
(
SUM(SalesAmount)
FOR Month IN (' + @columns + ')
) AS PivotTable;';

-- Step 4: Execute the dynamic SQL
EXEC sp_executesql @query;

-- drop temp table
DROP TABLE #MonthOrder;

Output:

Explanation:

1. Predefined Month Order Table:
   • A temporary table #MonthOrder defines the correct chronological order of months.
   • The month names in Sales are joined with this table to ensure proper ordering.
2. Dynamic Column Generation:
   • The list of months (@columns) is created by combining QUOTENAME for safety and sorting them based on their MonthOrder.
3. Dynamic SQL Execution:
   • The constructed query ensures that the months appear in the desired order.
4. Drop temp table:
   • The temporary table is dropped after execution.