Cari Blog Ini

04 Oktober 2013

Example of Pivot Table - 002

Apply To :
* Microsoft SQL Server 2008 R2





Example :

1. Try to run below code :

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

if EXISTS(select * from INFORMATION_SCHEMA.tables where table_name = 'Temp_Pivot_1') 
begin exec ( 'drop table Temp_Pivot_1' ) end


---Try to Create Temporary Table
CREATE TABLE [dbo].Temp_Pivot_1
(
Item varchar(100) ,
Customer varchar(100) ,
Quantity int
)
Go



---Try to Insert Dummy Data
Insert into Temp_Pivot_1 
select 'Item-001','Customer-01',6
union
select 'Item-002','Customer-02',6
union
select 'Item-003','Customer-03',2
union
select 'Item-004','Customer-04',7
union
select 'Item-005','Customer-05',3
union
select 'Item-006','Customer-06',1
union
select 'Item-007','Customer-07',4

---Example of Table
select * from Temp_Pivot_1



---Example-02 of Pivot 
SELECT * FROM 

SELECT Item, sum(Quantity) Quantity
FROM Temp_Pivot_1 
group by Item 
) Temp_Pivot_1
PIVOT
(
sum(Quantity)
FOR Item in ([Item-005],[Item-002],[Item-006],[Item-007])
) AS PivotTableA


----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------


2.  The results is