Ramesh Joshi
What is pivot table and unpivot table?
By Ramesh Joshi in .NET on Apr 25 2016
  • Ramesh Joshi
    Apr, 2016 25

    Pivot tables -are used to summarize and display the data, specially in case of report data by means of aggregating the values. Pivot table can be used if we want to display the unique values of the column of a table as the columns of another table. It turns the unique values of a specified column into another table columns. SELECT non-pivoted column,firstpivotedcolumn AS column name,secondpivotedcolumn AS column name,lastpivotedcolumn AS column name FROM(SELECT query that produces the data>)AS aliasforsourcequery PIVOT (aggregation function(column being aggregated) FOR column that contains the values that will become column headersIN ( firstpivotedcolumn,secondpivotedcolumn,last pivoted column) ) AS aliasforthepivottable (optional ORDER BY clause) Unpivot table:- Unpivot table is reverse of Pivot table as it rotate the columns of a table into the value of a column. For example, suppose we have a table say tbl_stdmarksdata whose structure us given below:-Create table tbl_stdmarksdata (studentname nvarchar(100), I int, II int, III int, IV int, V int)Query for inserting data in this table is given below:-Insert into tbl_stdmarksdata (studentname,I,II,III,IV,V) Select 'Vivek Johari',30,20,35, 40, 45 Union All Select 'Chandra Singh',30,20,35,44, 80 Union All Select 'Avinash Dubey',30,25,35,20, 39 Union All Select 'Pankaj Kumar',33,29,30, 60, 50After insert, the data in the table :-select * from tbl_stdmarksdata The Query for the Unpivot table will be as follow:-select studentname,Marks,Grade from tbl_stdmarksdata unpivot (Marks for Grade in (I,II,III,IV,V) ) as tblunpvt

    • 0


Most Popular Job Functions


MOST LIKED QUESTIONS