How to use Pivot in SQL server ?

The PIVOT operator :-

Beginning with SQL Server 2005 (almost 10 years ago!), Microsoft introduced the PIVOT operator in T-SQL. With that operator you can perform the same transformation (rows to columns) with just one native operator. 

This post intends to help T-SQL developers get started with PIVOT queries. Most business applications will need some sort of PIVOT queries and I am sure many of you must have come across pivoting requirements several times in the past.

Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance. It also help us to generate Multidimensional reporting.


How do I use the PIVOT operator to rotate data in SQL Server?

You use the PIVOT operator within your query’s FROM clause to rotate and aggregate the values in a dataset. The data is pivoted based on one of the columns in the dataset. Each unique value in that column becomes its own column, which contains aggregated pivoted data.

To better understand how this all works, let’s start with the basic syntax for a query that uses the PIVOT operator:

SELECT column_list
FROM table_expression
  PIVOT
  (
    aggregate_function(aggregate_column)
    FOR pivot_column
    IN( pivot_column_values )
  ) [AS] pivot_table_alias
[ORDER BY column_list];

For the SELECT clause, you can specify an asterisk (*) or the individual columns, and for the FROM clause, you can specify a table or table expression. If you use a table expression, then you must also define a table alias. You can also include an ORDER BY clause, but that’s optional. You’ll get to see these clauses in action as we progress through the questions. For now, let’s focus on the PIVOT clause. You need to understand how this clause works to make sure your pivots work the way you want.

After you specify the PIVOT keyword, you pass in what are essentially three arguments, enclosed in parentheses. The first is the aggregate function and the name of the column to be aggregated. You can use any aggregate function except the COUNT function, when used with an asterisk, as in COUNT(*).

Next, you define the FOR subclause, which specifies the column on which the pivot will be based. It is this column whose distinct values are turned into their own columns. The FOR subclause also includes the IN operator, which is where you specify the pivot column values that will be turned into columns. The values you specify here must exist in the pivot column or they will be ignored.


Example:-

Create a table Product:-

CREATE TABLE [dbo].[tblProduct](

       [fldProductName] [varchar](50) NULL,

       [fldProductPrice] [float] NULL,

       [fldYear] [int] NULL

)

Now insert some records:-

GO

INSERT [dbo].[tblProduct] ([fldProductName], [fldProductPrice], [fldYear])

 VALUES (N'Mobile Phone', 15000, 2012)

GO

INSERT [dbo].[tblProduct] ([fldProductName], [fldProductPrice], [fldYear])

 VALUES (N'Head Phone', 1200, 2013)

GO

INSERT [dbo].[tblProduct] ([fldProductName], [fldProductPrice], [fldYear])

 VALUES (N'Mobile Phone', 13000, 2013)

GO

INSERT [dbo].[tblProduct] ([fldProductName], [fldProductPrice], [fldYear])

 VALUES (N'Head Phone', 1100, 2012)

GO

INSERT [dbo].[tblProduct] ([fldProductName], [fldProductPrice], [fldYear])

 VALUES (N'Mobile Phone', 21000, 2012)

GO

INSERT [dbo].[tblProduct] ([fldProductName], [fldProductPrice], [fldYear])

 VALUES (N'Head Phone', 1100, 2012)

GO

Now here I will write a Pivot query:-

SELECT *

FROM [dbo].[tblProduct]

PIVOT(SUM([fldProductPrice])

FOR [fldProductName] IN ([Mobile Phone], [Head Phone])) AS PVTTable

Output:-

In the above article I try to explain the use of Pivot in SQL. I hope its useful to you. Please post your feedback, question, or comments about this article.