Sql Server Pivoting And Unpivoting Data Complete Guide

Example 1: Pivoting Data

Scenario: You have a table that stores the monthly sales of a company by product. You want to transform this table so that each product is represented as a column, making it easier to compare sales across different products.

Step 1: Create the Example Table

First, let's create a sample table named MonthlySales:

CREATE TABLE MonthlySales (
    ProductID INT,
    SaleMonth CHAR(3),
    SalesAmount DECIMAL(10,2)
);

INSERT INTO MonthlySales (ProductID, SaleMonth, SalesAmount) VALUES
(1, 'Jan', 450.00),
(1, 'Feb', 670.00),
(1, 'Mar', 380.00),
(2, 'Jan', 320.00),
(2, 'Feb', 540.00),
(2, 'Mar', 129.00);

The above script creates a MonthlySales table with three columns: ProductID, SaleMonth, and SalesAmount. It then inserts some example data.

Step 2: Pivot the Data

Now, let's use the PIVOT function to transform the sales data into columns:

SELECT 
    ProductID,
    Jan,
    Feb,
    Mar
FROM 
(
    SELECT 
        ProductID,
        SaleMonth,
        SalesAmount
    FROM 
        MonthlySales
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR SaleMonth IN ([Jan], [Feb], [Mar])
) AS PivotTable;

Explanation:

• The inner SELECT statement (aliased as SourceTable) selects the ProductID, SaleMonth, and SalesAmount from the MonthlySales table.
• The PIVOT statement transforms the SalesAmount rows into columns, where SaleMonth represents the column headers (Jan, Feb, Mar).
• The SUM(SalesAmount) aggregates the sales amount for each month per product.

Output:

ProductID   Jan       Feb       Mar
----------- --------- --------- ---------
1           450.00    670.00    380.00
2           320.00    540.00    129.00

Example 2: Unpivoting Data

Scenario: Consider you have a table with sales data broken down by product and month stored as columns. You want to restructure this table back to a format where each row represents a product's sales for a specific month.

Step 1: Create the Example Table

Let's assume we have a new table ProductsByMonthSales which includes the pivoted data:

CREATE TABLE ProductsByMonthSales (
    ProductID INT,
    Jan DECIMAL(10,2),
    Feb DECIMAL(10,2),
    Mar DECIMAL(10,2)
);

INSERT INTO ProductsByMonthSales (ProductID, Jan, Feb, Mar) VALUES
(1, 450.00, 670.00, 380.00),
(2, 320.00, 540.00, 129.00);

Step 2: Unpivot the Data

Now, let's use the UNPIVOT function to transform this table back to the original structure (rows):

SELECT 
    ProductID,
    SaleMonth,
    SalesAmount
FROM 
(
    SELECT 
        ProductID,
        Jan,
        Feb,
        Mar
    FROM 
        ProductsByMonthSales
) AS SourceTable
UNPIVOT
(
    SalesAmount
    FOR SaleMonth IN (Jan, Feb, Mar)
) AS UnpivotTable;

Explanation:

• The inner SELECT statement (aliased as SourceTable) selects all the columns representing the months of sales.
• The UNPIVOT statement converts the columns back into rows, using FOR SaleMonth IN (Jan, Feb, Mar) to specify the columns to unpivot.
• SalesAmount becomes the new value column for all unpivot operations.

Output:

ProductID   SaleMonth SalesAmount
----------- --------- -----------
1           Jan       450.00
1           Feb       670.00
1           Mar       380.00
2           Jan       320.00
2           Feb       540.00
2           Mar       129.00

Example 3: Dynamic Pivoting

If you don’t know in advance which months will be present in your data or if you have many months to pivot, you need to build a dynamic SQL query to handle this.

Step 1: Create the Example Table

Let’s stick with the same MonthlySales table created earlier.

Step 2: Generate the Dynamic Pivot Query

Dynamic pivoting requires building a SQL query string at runtime, based on the data. Here’s an example of how to do it:

DECLARE @DynamicPivotQuery NVARCHAR(MAX);
DECLARE @ColumnNameString NVARCHAR(MAX);

-- Get all distinct month names and prepare the column name string
SELECT @ColumnNameString = COALESCE(@ColumnNameString + ', [' + CAST(SaleMonth AS VARCHAR(10)) + ']','[' + CAST(SaleMonth AS VARCHAR(10)) + ']')
FROM (SELECT DISTINCT SaleMonth FROM MonthlySales) AS Months;

-- Construct the dynamic pivot query
SET @DynamicPivotQuery =
    N'SELECT ProductID, ' + @ColumnNameString +
    N' FROM MonthlySales ' +
    N'PIVOT (SUM(SalesAmount) FOR SaleMonth IN (' + @ColumnNameString + N')) AS PivotTable';

-- Execute the dynamic pivot query
EXEC sp_executesql @DynamicPivotQuery;

Explanation:

• First, we retrieve all distinct sale months from the MonthlySales table and concatenate them into a single string (@ColumnNameString). This string is used in both the SELECT and FOR clauses of the PIVOT.
• We construct the @DynamicPivotQuery string dynamically, substituting the concatenated column names.
• Finally, we execute the constructed query using sp_executesql.

Example 4: Dynamic Unpivoting

To perform dynamic unpivoting, you also need to generate the SQL query at runtime.

Step 1: Create the Example Table

Again, let's use the ProductsByMonthSales table.

Step 2: Generate the Dynamic Unpivot Query

Here’s how to dynamically unpivot:

DECLARE @DynamicUnpivotQuery NVARCHAR(MAX);
DECLARE @ColumnNameString NVARCHAR(MAX);

-- Get all distinct month names and prepare the column name string
SELECT @ColumnNameString = COALESCE(@ColumnNameString + ', ' + QUOTENAME(COLUMN_NAME), QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'ProductsByMonthSales' AND COLUMN_NAME NOT IN ('ProductID');

-- Construct the dynamic unpivot query
SET @DynamicUnpivotQuery =
    N'SELECT ProductID, SaleMonth, SalesAmount ' +
    N'FROM ProductsByMonthSales ' +
    N'UNPIVOT (SalesAmount FOR SaleMonth IN (' + @ColumnNameString + N')) AS UnpivotTable';

-- Execute the dynamic unpivot query
EXEC sp_executesql @DynamicUnpivotQuery;

Explanation:

• Using INFORMATION_SCHEMA.COLUMNS, we generate the list of month columns excluding ProductID.
• We concatenate these column names into a single string (@ColumnNameString).
• The @DynamicUnpivotQuery string is constructed to select the ProductID, dynamically generated column names representing SaleMonth, and SalesAmount in an unpivot operation.
• The constructed query is executed using sp_executesql.