what is pivot mechanism? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

what is pivot mechanism?

Author

Topic

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2008-12-18 : 05:53:18

hello sql team...
plese tell me that "what is pivoting and unpivoting...?" in SQL Server2005.
i want a clear explenation with simple queries.
that means..

you can take some numbers table.(for simple understainding,not
a complex relations)

hope this takes easy..
i am waiting for your kind replies..
thank you..

Srinivas.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-18 : 06:00:20

see in books online once u will get some examples also

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-18 : 07:02:26

Example for Pivot
CREATE TABLE dbo.tbl_PurchasingOrders
(
PurchaseOrderID INT, EmployeeID INT, VendorID INT
)

INSERT INTO tbl_PurchasingOrders
SELECT 4,70118,102
UNION ALL
SELECT 5,70116,102
UNION ALL
SELECT 2,70115,101

SELECT * FROM tbl_PurchasingOrders

SELECT VENDORID , [70118] AS EMPID1 ,[70116] AS EMPID2 , [70115] AS EMPID3
FROM
(SELECT PurchaseOrderID , EmployeeID ,VENDORID FROM tbl_PurchasingOrders) P
PIVOT (COUNT(PurchaseOrderID) FOR EmployeeID IN ([70118] ,[70116] , [70115] ))
AS PVT

Example for unpivot
CREATE TABLE pvtsample (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
INSERT INTO pvtsample VALUES (1,4,3,5,4,4)
INSERT INTO pvtsample VALUES (2,4,1,5,5,5)
INSERT INTO pvtsample VALUES (3,4,3,5,4,4)
INSERT INTO pvtsample VALUES (4,4,2,5,5,4)
INSERT INTO pvtsample VALUES (5,5,1,5,5,5)

--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
Try this examples

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2008-12-18 : 08:02:34

thanks bklr,
i can try it today...

thanks & regards
gsrinivas

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-18 : 10:42:16