Pivoting - tricky example in t-sql

 Let's create a tricky pivoting example in T-SQL that involves more complex transformations, such as incorporating additional calculations and handling potential NULL values.

Scenario

Suppose we have a Sales table with the following structure and data:

Year	Quarter	Region	SalesAmount
2022	Q1	East	100
2022	Q1	West	200
2022	Q2	East	150
2022	Q2	West	250
2022	Q3	East	200
2022	Q3	West	300
2022	Q4	East	250
2022	Q4	West	350
2023	Q1	East	110
2023	Q1	West	210
2023	Q2	East	160
2023	Q2	West	260
2023	Q3	East	210
2023	Q3	West	310
2023	Q4	East	260
2023	Q4	West	360

We want to pivot this data to show the total sales for each region by year, and include columns for the percentage of total sales for each quarter within each year.

Pivoting with Calculations Example

Step 1: Create a Temporary Table for Summed Data

First, create a temporary table that includes the total sales for each quarter by region and the overall total sales for each year.

-- Create a temporary table to store summed data
SELECT 
    Year, 
    Quarter, 
    Region, 
    SalesAmount,
    SUM(SalesAmount) OVER (PARTITION BY Year) AS TotalSalesYear
INTO #SalesSummary
FROM Sales;

Step 2: Construct the Dynamic SQL Query for Pivoting

Construct the dynamic SQL query to pivot the data and include the percentage calculations.

DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- Get unique regions and construct column names
SELECT @Columns = STRING_AGG(QUOTENAME(Region), ', ')
FROM (SELECT DISTINCT Region FROM Sales) AS Regions;

-- Construct the dynamic SQL query
SET @SQL = '
SELECT Year, ' + @Columns + ',
    ' + @Columns + 'Q1_Percent, ' + @Columns + 'Q2_Percent, ' + @Columns + 'Q3_Percent, ' + @Columns + 'Q4_Percent
FROM
(
    SELECT 
        Year, 
        Region, 
        SUM(SalesAmount) AS TotalSales,
        SUM(CASE WHEN Quarter = ''Q1'' THEN SalesAmount END) AS Q1_Sales,
        SUM(CASE WHEN Quarter = ''Q2'' THEN SalesAmount END) AS Q2_Sales,
        SUM(CASE WHEN Quarter = ''Q3'' THEN SalesAmount END) AS Q3_Sales,
        SUM(CASE WHEN Quarter = ''Q4'' THEN SalesAmount END) AS Q4_Sales,
        SUM(SalesAmount) * 100.0 / NULLIF(SUM(SUM(SalesAmount)) OVER (PARTITION BY Year), 0) AS TotalSalesPercent
    FROM #SalesSummary
    GROUP BY Year, Region
) AS SourceTable
PIVOT
(
    MAX(TotalSales) FOR Region IN (' + @Columns + ')
) AS PivotTable
PIVOT
(
    MAX(TotalSalesPercent) FOR Region IN (' + @Columns + 'Q1_Percent, ' + @Columns + 'Q2_Percent, ' + @Columns + 'Q3_Percent, ' + @Columns + 'Q4_Percent)
) AS PercentTable
ORDER BY Year;
';

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

Explanation

1. Temporary Table with Summed Data: The #SalesSummary table includes the total sales for each quarter by region and the overall total sales for each year. The SUM(SalesAmount) OVER (PARTITION BY Year) calculates the total sales for the entire year for use in the percentage calculation.

2. Dynamic Columns: The STRING_AGG function constructs a comma-separated list of regions to dynamically generate the pivot column names.

3. Dynamic SQL Query: The @SQL variable contains the dynamic SQL query that performs the pivot operation and includes additional calculations for each quarter's sales percentage within the year. The CASE statements within the query handle the different quarters, and the NULLIF function prevents division by zero errors.

4. Pivoting and Calculations: The PIVOT operation is used twice:

   • First, to pivot the total sales data.
   • Second, to pivot the percentage calculations for each quarter.

5. Execution: The sp_executesql stored procedure executes the dynamically constructed SQL query.

This example showcases how to dynamically pivot data while incorporating additional calculations, handling potential NULL values, and generating the pivot columns based on the data at runtime.