Pivot and Unpivot in SQL Server??

Question

How to use that?

Answer 1

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

Answer 2

Pivot provides an easy mechanism in Sql Server to transform rows into columns.

Unpivot is the reversal of the Pivot operation. It basically provides a mechanism for transforming columns into rows.

Example

-- Creating Test Table
CREATE TABLE Example(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Example(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Example(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Example(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Example(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Example(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Example(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Example(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO

-- Selecting and checking entires in table
SELECT * FROM Example
GO

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

-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Example) 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
GO

-- Clean up database
DROP TABLE Example
GO