top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Pivot and Unpivot in SQL Server??

+2 votes
405 views
Pivot and Unpivot in SQL Server??
posted Jan 19, 2014 by Vishvachi Tiwari

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+2 votes
 
Best answer

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 Jan 19, 2014 by Atul Mishra
+1 vote

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

answer Nov 13, 2014 by Manikandan J
...