مدونة إبراهيم مصطفى

– Create cross-tabulation queries by using the PIVOT and UNPIVOT clauses.
– ————————————————————————-
– PIVOT and UNPIVOT
– —————–
– PIVOT transforms rows into columns (values to columns)
– FOR clause identifies column whose values will be pivoted

– EX:
Create table SalesOrder
(Customer varchar(20),product varchar(20),Qty int )

Insert SalesOrder
values(’Mike’,'Bike’,3)

Insert SalesOrder
values (’Mike’,'watch’,2)

Insert SalesOrder
values (’Mike’,'Bike’,5)

Insert SalesOrder
values (’lisa’,'Bike’,3)

Insert SalesOrder
values (’lisa’,'watch’,3)

Insert SalesOrder
values (’lisa’,'watch’,4)

SELECT * FROM Salesorder

SELECT * FROM Salesorder
PIVOT (SUM(Qty) FOR Product IN (Bike,watch)) PVT
–order by customer desc

———————–

– UNPIVOT (columns to values)
– Use the last query to create a yearly pivot table

Create table SalesorderUPVT
(Customer varchar(20),bike int,watch int)

Insert SalesorderUPVT
values(’lisa’,3,7)

Insert SalesorderUPVT
values (’mike’,8,2)

select * from SalesorderUPVT

SELECT Customer, Qty
FROM SalesorderUPVT
UNPIVOT (Qty FOR Prod IN (Bike,watch)) UnPVT