Friday, August 6, 2010

Understanding the PIVOT Operator in SQL Server 2008.

The Database users might need to view data in a user-defined format. These reports might involve summarizing data on the basis of various criteria. SQL Server 2008 allows you to generate summarized data report using the PIVOT clause of SELECT Statement.
The PIVOT Operator is used to transform a set of columns into values. PIVOT rotates a table –valued expression by turning the unique value from one column in the expression into multiple columns in the output, in addition. It also performs aggregations on the remaining column values if required in the output.

The syntax for  PIVOT Operator is:

SELECT* from table _ name
PIVOT (aggregation _ function (value _ column)
For PIVOT   _ column
IN (column _ list ) )
Table_ alias.

Query of PIVOT Operator.

CREATE TABLE Product
(
Cust VARCHAR(25),
Product VARCHAR(20),
QTY INT
)

INSERT INTO Product VALUES('KATE','VEG',2)
INSERT INTO Product VALUES('KATE','SODA',6)
INSERT INTO Product VALUES('KATE','MILK',1)
INSERT INTO Product VALUES('KATE','BEER',12)
INSERT INTO Product VALUES('FRED','MILK',3)
INSERT INTO Product VALUES('FRED','BEER',24)
INSERT INTO Product VALUES('KATE','VEG',3)

-- 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

The result is displayed below with PIVOT Table ordered 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

The result is displayed below with PIVOT Table ordered by CUST.

No comments:

Recent Posts