SQL Server - What is Pivot table ?? - Asked By rajesh kumar on 17-Feb-12 02:25 AM

Can any one explain me about PIVOT table ??

A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values.
Pivot tables are easily created by office users using Microsoft Excel or MS Access.
Since pivot table enables report builders and BI (Business Intelligence) specialists empower their presentation of reports and increase the visibility and understandability of mined data, pivot tables are common and preferred widely.

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.

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 an other 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

Example SQL server 2005:

Now the script using Pivot table is:

Output:

Pivot is feature of sql server so you can using pivot/unpivot get the data from row to column and vice-versa.
See this is good example of pivot here
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ 

hi,

Pivot table:

pivot table is a data summarization tool found in data visualization programs such as http://en.wikipedia.org/wiki/Spreadsheet or http://en.wikipedia.org/wiki/Business_intelligence software. Among other functions, pivot-table tools can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data. Pivot tables are also useful for quickly creating unweighted http://en.wikipedia.org/wiki/Cross_tabulation. The user sets up and changes the summary's structure by http://en.wikipedia.org/wiki/Drag_and_drop fields graphically. This "rotation" or pivoting of the summary table gives the concept its name. The term pivot table is a generic phrase used by multiple vendors. However, http://en.wikipedia.org/wiki/Microsoft_Corporation has http://en.wikipedia.org/wiki/Trademark the specific form PivotTable

Read more:http://en.wikipedia.org/wiki/Pivot_table


Regards,

What Is  Pivot ?

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Where we use the Pivot ?
we use it on table valued expression in to another. it rotates table value expression using unique value in to multiple column in out and perform aggeration

Syntax

The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements.

The following is annotated syntax for PIVOT.

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    ...

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

How to use the Pivot ?
Example

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

Hi..

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.http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ with example.