Pivot and Unpivot data in Sql server

Pivot and Unpivot data

Pivot example in sql server

hi,
     first we will see what is pivot and unpivot,uses in sql server 

 

Q: What is Pivot?

ans:     A Pivot or pivoting data means get row data to column.

Systax of pivot query
    select * from tableName  pivot(agreegateFunction(field want to pivot) for column_Name in (row_data1,row_data2 so on) )

    ******** I can explain with example***********

for your better Understanding create a following tables
      create table color (comid int, id int,Names varchar(50))
      create table Quantity ( Qid int, id int,Quantity int)

Insert data in both tables

      insert into color (comid,id,Names) values (1,1,''black'')
     insert into color (comid,id,Names) values (1,2,''red'')
     insert into color (comid,id,Names) values (2,3,''wt'')
     insert into color (comid,id,Names) values (2,4,''re'')
     insert into color (comid,id,Names) values (2,5,''Grey'')
     insert into Quantity (Qid,id,Quantity ) values (1,1,20)
     insert into Quantity (Qid,id,Quantity ) values (2,1,20)
     insert into Quantity (Qid,id,Quantity ) values (3,2,25)
     insert into Quantity (Qid,id,Quantity ) values (4,2,25)
     insert into Quantity (Qid,id,Quantity ) values (5,3,30)
     insert into Quantity (Qid,id,Quantity ) values (6,3,30)
     insert into Quantity (Qid,id,Quantity ) values (7,4,35)
     insert into Quantity (Qid,id,Quantity ) values (8,4,35)
     insert into Quantity (Qid,id,Quantity ) values (9,5,3)
     insert into Quantity (Qid,id,Quantity ) values (9,5,3)

Write a final query for pivot a data

select * from ( select comname,Names,Quantity from color a inner join company c on c.comid=a.comid inner join Quantity b on a.id=b.id ) DataTable PIVOT ( SUM(Quantity) FOR Names IN ( black,red,Grey,wt,re ) ) PivotTable

Q: What is UnPivot?

ans:     A UnPivot is vice versa of pivot it means it is used when we want to get
Colunm data to rows 

 

Q: What is UnPivot?

ans:     A UnPivot is vice versa of pivot it means it is used when we want to get
Colunm data to rows 

 

syntax of Unpivot data ::
select * from ( select * from comdetail ) DataTable UNPIVOT (Quantity FOR Names IN (black,red,Grey,wt,re) ) AS Unpvt