A pivot table is a program tool that allows you to reorganize and summarize selected columns and rows of data in a spreadsheet or database table to obtain a desired report. A pivot table doesn't actually change the spreadsheet or database itself. In database lingo, to pivot is to turn the data (see slice and dice) to view it from different perspectives.

 A pivot table is especially useful with large amounts of data. For example, a store owner might list monthly sales totals for a large number of merchandise items in an Excel spreadsheet. If the owner wanted to know which items sold better in a particular financial quarter, it would be very time-consuming for her to look through pages and pages of figures to find the information. A pivot table would allow the owner to quickly reorganize the data and create a summary for each item for the quarter in question.

Pivot tables display data in tabular form. The pivot table formatting is not different than a tabular report formatting but the table columns are formed by the report data itself. I mean as a pivot table example, your report creator can build a report with years and months in the left side of the table, the main product lines are displayed as columns, and total sales of each product line in the related year and month is displayed in the cell content.

Usage:-

Microsoft SQL Server has introduced the PIVOT and UNPIVOT commands as enhancements to t-sql with the release of MS SQL Server 2005. In MS SQL Server 2008, we can still use the PIVOT command and UNPIVOT command to build and use pivot tables in Sql. T-SQL Pivot and Unpivot statements will transform and rotate a tabular data into another table value data in Sql.Since Pivot / Unpivot are SQL2005 t-Sql enhancements, databases which you want to execute pivot and unpivot commands should be at least at compatibility level 90 (SQL2005) or 100 (SQL2008).

 T-SQL Pivot Syntax

T-SQL PIVOT syntax is not explicitly identified in the MSDN or on SQL Server BOL (Books Online) but general use of Pivot command can be summarized as follows:

SELECT  [non-pivoted column], -- optional  [additional non-pivoted columns], -- optional

  [first pivoted column],  [additional pivoted columns]

FROM (

  SELECT query producing sql data for pivot  -- select pivot columns as dimensions and

  -- value columns as measures from sql tables) AS Table Alias PIVOT (

  (column for aggregation or measure column) -- MIN,MAX,SUM,etc  FOR []  IN (

    [first pivoted column], ..., [last pivoted column]  )) AS PivotTableAliasORDER BY clause -- optional