Using PIVOT in SQL Server 2005 (Crosstab queries to change rows to column in a table or to rotate a table)
Rotating a table (ie. To turn the values of a specified column into column names) is one of the most completed task prior to SQL 2005. From SQL 2005, this complex operation cab be done using PIVOT operator easily.
A Pivot Table automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator in SQL Server turns the values of a specified column into column names, effectively rotating a table.
Let us see below example to understand it clearly and also to get an example of this feature:
If we want to report this data, one of the ideal solution will be to list the Products across the top for each Name and then report the amount sold for each product per person. It should look as below:
To complete this, there is no direct way with out using Pivot. (This is actually rotating the table and Making each Product Name as column name).
Below code will actually complete the operation:
(Please refer to the comments to understand what the part of the code actually does)
-- This is the final output columns. Here we are deciding Name and which product will be reported as column header
SELECT [Name] as Employee, [A] AS ProductA, [B] AS ProductB, [C] as ProductC
FROM
-- Picking raw data and storing them in temptable called tempvt
(SELECT Name, Product, Amount FROM [tbl_SalesMaster] ) tempvt
-- Actual logic of Pivot where we are summing the sales amount for each product for each employee
PIVOT
(
SUM (Amount)
FOR Product IN
( [A], [B],[C])
) AS pvt
Now if you need to get the source code of the table used for this example, please refer below:
CREATE TABLE [dbo].[tbl_SalesMaster](
[Name] [nchar](10) NOT NULL,
[Product] [nchar](10) NOT NULL,
[Amount] [int] NULL
) ON [PRIMARY]
GO
Comments