PIVOT and UNPIVOT are powerful operations in SQL Server that allow you to transform data from rows to columns and vice versa. However, it’s important to understand that these operations may not always result in the exact original table.

Let’s take a look at an example to understand this concept better:

USE AdventureWorks

-- Creating Test Table
CREATE TABLE Product (
    Cust VARCHAR(25),
    Product VARCHAR(20),
    QTY INT
)

-- Inserting Data into Table
INSERT INTO Product (Cust, Product, QTY)
VALUES ('KATE', 'VEG', 2),
       ('KATE', 'SODA', 6),
       ('KATE', 'MILK', 1),
       ('KATE', 'BEER', 12),
       ('FRED', 'MILK', 3),
       ('FRED', 'BEER', 24),
       ('KATE', 'VEG', 3)

-- Selecting and checking entries in table
SELECT * FROM Product

-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
    SELECT CUST, PRODUCT, QTY
    FROM Product
) up
PIVOT (
    SUM(QTY)
    FOR CUST IN (FRED, KATE)
) AS pvt
ORDER BY PRODUCT

-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
    SELECT CUST, PRODUCT, QTY
    FROM Product
) up
PIVOT (
    SUM(QTY)
    FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS pvt
ORDER BY CUST

-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM (
    SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
    FROM (
        SELECT CUST, PRODUCT, QTY
        FROM Product
    ) up
    PIVOT (
        SUM(QTY)
        FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
    ) AS pvt
) p
UNPIVOT (
    QTY
    FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt

-- Clean up database
DROP TABLE Product

In the above example, we have a table called “Product” with columns for customer, product, and quantity. We perform a PIVOT operation to transform the table based on the customer and product columns, using the SUM function to aggregate the quantities. Then, we perform an UNPIVOT operation to revert the table back to its original form.

However, if we closely examine the final PIVOT-UNPIVOT table, we can see that it is slightly different from the original table. The values in the red font indicate that they have been summed up. This is because we used the SUM function in the PIVOT operation, which aggregated the values. Therefore, in this case, we cannot obtain the exact original table.

It’s important to note that if aggregate functions were not applied to the data or if the data was in a form that the aggregate function did not make any difference, we would be able to retrieve the original table.

PIVOT and UNPIVOT operations are powerful tools in SQL Server for data transformation. However, it’s crucial to understand their limitations and use them appropriately based on the specific requirements of your data.