Pivot and Unpivot using SQL and Dynamic SQL

SQL Server, the PIVOT and UNPIVOT operators are used to transform data from rows into columns (PIVOT) or from columns into rows (UNPIVOT).

Here's an example of how you can use PIVOT:

Let's say you have a table Sales with the following columns: Product, Month, and Revenue. You want to pivot the data to show total revenue for each product across different months.

-- Sample data
CREATE TABLE Sales (
    Product VARCHAR(50),
    Month VARCHAR(50),
    Revenue DECIMAL(10, 2)
);

INSERT INTO Sales (Product, Month, Revenue)
VALUES
    ('Product_A', 'January', 1000),
    ('Product_A', 'February', 1500),
    ('Product_A', 'March', 2000),
    ('Product_B', 'January', 1200),
    ('Product_B', 'February', 1600),
    ('Product_B', 'March', 1800);

-- Pivot query
SELECT *
FROM (
    SELECT Product, Month, Revenue
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Revenue)
    FOR Month IN ([January], [February], [March])
) AS PivotTable;

This query will pivot the data to show total revenue for each product across different months.

And here's an example of how you can use UNPIVOT:

Let's say you have a table Sales_Pivot with the following columns: Product, January, February, March, and you want to unpivot this data to show the revenue for each product in each month.

sql

-- Sample data
CREATE TABLE Sales_Pivot (
    Product VARCHAR(50),
    January DECIMAL(10, 2),
    February DECIMAL(10, 2),
    March DECIMAL(10, 2)
);

INSERT INTO Sales_Pivot (Product, January, February, March)
VALUES
    ('Product_A', 1000, 1500, 2000),
    ('Product_B', 1200, 1600, 1800);

-- Unpivot query
SELECT Product, Month, Revenue
FROM (
    SELECT Product, January, February, March
    FROM Sales_Pivot
) AS SourceTable
UNPIVOT (
    Revenue FOR Month IN ([January], [February], [March])
) AS UnpivotTable;

This query will unpivot the data to show the revenue for each product in each month

 Dynamic SQL example

Dynamic SQL in SQL Server allows you to construct SQL statements at runtime, which can be useful for cases where the structure of your pivot or unpivot operation may change dynamically. Here's an example of using dynamic SQL with PIVOT and UNPIVOT:

First, let's start with a dynamic pivot example:

sql

-- Sample data
CREATE TABLE Sales (
    Product VARCHAR(50),
    Month VARCHAR(50),
    Revenue DECIMAL(10, 2)
);

INSERT INTO Sales (Product, Month, Revenue)
VALUES
    ('Product_A', 'January', 1000),
    ('Product_A', 'February', 1500),
    ('Product_A', 'March', 2000),
    ('Product_B', 'January', 1200),
    ('Product_B', 'February', 1600),
    ('Product_B', 'March', 1800);

-- Dynamic pivot query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(Month) 
                    FROM Sales
                    FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT Product, ' + @cols + ' 
            FROM 
            (
                SELECT Product, Month, Revenue
                FROM Sales
            ) x
            PIVOT 
            (
                SUM(Revenue)
                FOR Month IN (' + @cols + ')
            ) p '

EXEC sp_executesql @query;

This dynamic pivot query will pivot the data to show total revenue for each product across different months.

Now, let's see an example of dynamic unpivot:

sql

-- Sample data
CREATE TABLE Sales_Pivot (
    Product VARCHAR(50),
    January DECIMAL(10, 2),
    February DECIMAL(10, 2),
    March DECIMAL(10, 2)
);

INSERT INTO Sales_Pivot (Product, January, February, March)
VALUES
    ('Product_A', 1000, 1500, 2000),
    ('Product_B', 1200, 1600, 1800);

-- Dynamic unpivot query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(Month) 
                    FROM (VALUES ('January'), ('February'), ('March')) AS Months(Month)
                    FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT Product, Month, Revenue
            FROM 
            (
                SELECT Product, January, February, March
                FROM Sales_Pivot
            ) x
            UNPIVOT 
            (
                Revenue FOR Month IN (' + @cols + ')
            ) unpvt'

EXEC sp_executesql @query;

This dynamic unpivot query will unpivot the data to show the revenue for each product in each month.