Saturday 4 May 2013

Pivot Unpivot data in Sql server

Pivot 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

10 comments:

  1. Good example

    ReplyDelete
  2. it is very much helpful for me....
    Thanks

    ReplyDelete
  3. a nice example sir ,Can you please send me link of every new post of your blog

    ReplyDelete
  4. if you want to get my posts by mail then ,add your email id in get every New post by mail
    box and click to submit.Thanks

    ReplyDelete
  5. very nice article.

    ReplyDelete
  6. Msg 208, Level 16, State 1, Line 1
    Invalid object name 'comdetail'.

    ReplyDelete
    Replies
    1. you need to create comdetail table ,a comdetail is name of table .so use your table name

      Delete
  7. What are the column that are used in the creation of the company table and commdetail tables

    ReplyDelete
    Replies
    1. you can use below script for creating company table
      -------------------------------------------------------------
      create table company(comid int, comname varchar(100))

      insert into company (comid,comname) values (1,'asian')
      insert into company (comid,comname) values (2,'india paints')
      insert into company (comid,comname) values (2,'colursPaints')
      -----------------------------------------------------------------------
      create comdetail table by using below script
      ----------------------------------------------------------------------------------
      select * into comdetail 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
      ---------------------------------------------------------------------------------------------------

      Delete
  8. Good day,

    I have a question, created a unpivot and i want the result of the unpivot the way i created a select before creating the unpivot, coz my result now is sorter alphabetically

    thanks

    ReplyDelete

if you have any doubt any suggestions do comment