Pivot And Unpivot In SQL Server

Pivot is converting rows into columns.

Unpivot is opposite of pivot which converts column into rows.

Part -2

 

2. Unpivot Example

Now, suppose you have a pivoted table PivotedSales:

ProductID	Q1	Q2	Q3	Q4
1	1000	1500	2000	2500
2	3000	3500	4000	4500

You want to transform it back into the original format.

SELECT ProductID, Quarter, SalesAmount
FROM
(
    SELECT ProductID, Q1, Q2, Q3, Q4
    FROM PivotedSales
) AS SourceTable
UNPIVOT
(
    SalesAmount
    FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS UnpivotTable;

Output:

ProductID	Quarter	SalesAmount
1	Q1	1000
1	Q2	1500
1	Q3	2000
1	Q4	2500
2	Q1	3000
2	Q2	3500
2	Q3	4000
2	Q4	4500

In SQL Server, PIVOT and UNPIVOT are relational operators used to transform data between rows and columns, making it easier to analyze and present data in a more structured format.

PIVOT:
The PIVOT operator converts row-level data into a columnar format. It is typically used to aggregate data and rotate rows into columns, making it useful for creating summary tables or cross-tab reports.

Example

SELECT Product, [2021], [2022]
FROM (
    SELECT Product, Year, SalesAmount
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(SalesAmount)
    FOR Year IN ([2021], [2022])
) AS PivotTable;

UNPIVOT:
The UNPIVOT operator does the opposite of PIVOT. It converts columnar data back into rows. This is useful when you need to normalize data or restructure it for further analysis.

Example

SELECT Product, Year, SalesAmount
FROM (
    SELECT Product, [2021], [2022]
    FROM PivotedSales
) AS SourceTable
UNPIVOT (
    SalesAmount FOR Year IN ([2021], [2022])
) AS UnpivotTable;

Key Points:
    PIVOT is used to aggregate and rotate rows into columns.
    UNPIVOT is used to convert columns back into rows.

Both operations are useful for reshaping data to meet specific reporting or analysis needs.

PIVOT and UNPIVOT are powerful operations used to transform data and make it more readable, efficient, and manageable.

These operations allow us to manipulate tables by switching between rows and columns, which can be crucial for summarizing data, reporting, and data analysis.

Understanding how to use PIVOT and UNPIVOT operators effectively can significantly enhance our data manipulation capabilities.

Sample table:

CREATE TABLE ProductSales (
    Product NVARCHAR(50),
    Year INT,
    Sales INT
);

INSERT INTO ProductSales (Product, Year, Sales)
VALUES
    ('Product1', 2020, 10000),
    ('Product1', 2021, 15000),
    ('Product2', 2020, 20000),
    ('Product2', 2021, 25000);

SELECT * FROM ProductSales;

The 'PIVOT' operator enables you to rotate your data from a state of rows to a state of columns. This is particularly useful when you want to generate a summary report from your data.

To generate a summary report that shows the sales of each product by year, you can use the 'PIVOT' operator as follows:

SELECT *
FROM (
    SELECT Product, Year, Sales
    FROM ProductSales
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Year IN ([2020], [2021])
) AS PivotTable;

UNPIVOT allows you to convert your columns into rows. This is particularly useful when you need to normalize your data.

You can use the UNPIVOT operator to convert the pivoted data back to the original table format

-- Step 1: Pivot the data and store it in a temporary table
SELECT * INTO #PivotedTable
FROM (
    SELECT Product, Year, Sales
    FROM ProductSales
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Year IN ([2020], [2021])
) AS PivotTable;

-- Step 2: Unpivot the data back to its original format
SELECT Product, Year, Sales
FROM #PivotedTable
UNPIVOT (
    Sales FOR Year IN ([2020], [2021])
) AS UnpivotedTable;

-- Step 3: Drop the temporary table after use
DROP TABLE #PivotedTable;