SQL Server - PIVOT Query

Asked By kiruba .e on 19-Nov-11 03:46 AM

Hi,
I want to about pivot query in sql server.

What is pivot?
Where we use that?
How to use that in sql query?

Give some example for pivot query...

Thanks
Regards
Kiruba.e

Chintan Vaghela replied to kiruba .e on 19-Nov-11 03:51 AM

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.
USE AdventureWorks
GO
SELECT [CA], [AZ], [TX]
FROM
(
SELECT sp.StateProvinceCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
) p
PIVOT
(
COUNT (StateProvinceCode)
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
) AS pvt;

DL M replied to kiruba .e on 19-Nov-11 03:58 AM

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.

USE AdventureWorks



GO



SELECT [CA], [AZ], [TX]



FROM



(



SELECT sp.StateProvinceCode



FROM Person.Address a



INNER JOIN Person.StateProvince sp



ON a.StateProvinceID = sp.StateProvinceID



) p



PIVOT



(



COUNT (StateProvinceCode)



FOR StateProvinceCode



IN ([CA], [AZ], [TX])



) AS pvt;

Sunil Darji replied to kiruba .e on 19-Nov-11 04:02 AM

1. Pivots can convert row into column data. Pivots are frequently used in reports, and are reasonably easy to work with

With Example

   Create 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)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO

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

Result of Query

-- Pivot Table ordered by PRODUCT PRODUCT FRED KATE ------- ----- -------- ----------- BEER 24 12 MILK 3 1 SODA NULL 6 VEG NULL 5

Run above query step by step query and check how pivot works

Sunil Darji replied to kiruba .e on 19-Nov-11 04:03 AM

1. Pivots can convert row into column data. Pivots are frequently used in reports, and are reasonably easy to work with

With Example

   Create 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)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO

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

Result of Query

-- Pivot Table ordered by PRODUCT PRODUCT FRED KATE ------- ----- -------- ----------- BEER 24 12 MILK 3 1 SODA NULL 6 VEG NULL 5

Run above query step by step query and check how pivot works

Suchit shah replied to kiruba .e on 19-Nov-11 04:08 AM

What Is Pivot ?
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.

Where we use the Pivot ?
we use it on table valued expression in to another. it rotates table value expression using unique value in to multiple column in out and perform aggeration

Syntax

The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements.

The following is annotated syntax for PIVOT.

SELECT <non-pivoted column>,

[first pivoted column] AS <column name>,

[second pivoted column] AS <column name>,

...

[last pivoted column] AS <column name>

FROM

(<SELECT query that produces the data>)

AS <alias for the source query>

PIVOT

(

<aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

How to use the Pivot ?
Example

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

Hope it helps

kiruba .e replied to Suchit shah on 19-Nov-11 04:35 AM

is this temporary table?

Suchit shah replied to kiruba .e on 19-Nov-11 04:43 AM

No it is not a Temporary table it is just use for the Column heading..
The following code example produces a two-column table that has four rows.

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;

Here is the result set.

DaysToManufacture AverageCost

0 5.0885

1 223.88

2 359.1082

4 949.4105

The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. A column is provided for three [3] days, even though the results are NULL.

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

Here is the result set.

Cost_Sorted_By_Production_Days 0 1 2 3 4

AverageCost 5.0885 223.88 359.1082 NULL 949.4105