Cross Tab Queries using PIVOT in SQL

Some times we need to convert row to column in SQL. We can do that by using basic SQL query. But most of the time it may complex. For that MS SQL Server provides a built-in mechanism,PIVOT. This article describes a simple way to use pivot in SQL query. Summary of the article:

• What is PIVOT Queries?
• Create Pivoted Tables
• Simple Pivot Queries
• Output of PIVOT Query
• How PIVOT Query Works?
• Limitation of PIVOT Queries

What is PIVOT Queries?
Microsoft introduced the PIVOT operator in SQL Server 2005. By using PIVOT queries we can convert row to column. This helps us to create pivoted tables. Suppose we want to generate a report that breaks down sales by year and months so that we can compare our sales for every month.

Create Pivoted Tables
Let us consider a Sales table which has three columns, InvoiceNo, InvoiceDate, Amount. Suppose we have data inserted into the table. Our goal is to display the year and month wise sum of Amount. Use the following SQL scripts to create a sample sales table and for some sample data.

CREATE TABLE [dbo].[Sales](
 [InvoiceNo] [bigint] IDENTITY(1,1) NOT NULL,
 [InvoiceDate] [datetime] NULL,
 [Amount] [decimal](18, 0) NULL,
 CONSTRAINT [PK_Collections] PRIMARY KEY CLUSTERED 
(
 [InvoiceNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Sales] ON
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (1, CAST(0x0000A2B900000000 AS DateTime), CAST(100 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (2, CAST(0x0000A2CE00000000 AS DateTime), CAST(200 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (3, CAST(0x0000A2EF00000000 AS DateTime), CAST(100 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (4, CAST(0x0000A30900000000 AS DateTime), CAST(400 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (5, CAST(0x0000A32700000000 AS DateTime), CAST(100 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (6, CAST(0x0000A34600000000 AS DateTime), CAST(600 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (7, CAST(0x0000A36600000000 AS DateTime), CAST(200 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (8, CAST(0x0000A38900000000 AS DateTime), CAST(100 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (9, CAST(0x0000A14C00000000 AS DateTime), CAST(500 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (10, CAST(0x0000A16B00000000 AS DateTime), CAST(100 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (11, CAST(0x0000A18700000000 AS DateTime), CAST(100 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (12, CAST(0x0000A1A600000000 AS DateTime), CAST(600 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (13, CAST(0x0000A1C400000000 AS DateTime), CAST(100 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (14, CAST(0x0000A1E300000000 AS DateTime), CAST(200 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (15, CAST(0x0000A20100000000 AS DateTime), CAST(900 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (16, CAST(0x0000A22000000000 AS DateTime), CAST(100 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (17, CAST(0x0000A23F00000000 AS DateTime), CAST(100 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (18, CAST(0x0000A25D00000000 AS DateTime), CAST(800 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (19, CAST(0x0000A27C00000000 AS DateTime), CAST(800 AS Decimal(18, 0)))
INSERT [dbo].[Sales] ([InvoiceNo], [InvoiceDate], [Amount]) VALUES (20, CAST(0x0000A29A00000000 AS DateTime), CAST(100 AS Decimal(18, 0)))
SET IDENTITY_INSERT [dbo].[Sales] OFF

Simple Pivot Queries
Write the following SQL queries to get PIVOT output

SELECT *
FROM (
      SELECT
            YEAR(InvoiceDate) AS Years
            ,LEFT(DATENAME(month, InvoiceDate), 3) AS Months
            ,Amount
      FROM         Sales
      ) S
PIVOT
(
    SUM(Amount)
    FOR Months IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
)AS Pvot

Output of PIVOT Query
If we execute the above query we will get the following output:

How PIVOT Query Works
Every PIVOT query has three basic parts:

1. Base Query
2. PIVOT expression
3. Main Select Query

Base Query
SELECT YEAR(InvoiceDate) AS Years ,LEFT(DATENAME(month, InvoiceDate), 3) AS Months ,Amount FROM Sales
This query pulls the row data. Here we need to select only the necessary columns. Unnecessary columns will create extra grouping. This query creates temporary tables which will be used in the next step.

PIVOT expression
PIVOT(SUM(Amount)FOR Months IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec))AS Pvot
This expression does the actual summarization and puts the results into a temporary table called Pvot. First part of the PIVOT expression is an aggregate function and the function results will show up under the pivoted columns. After the aggregate function, we need to type the keyword FOR followed by the name of the pivoted column. To determine the pivoted column, we need to figure out which column contains the values that we want displayed as column headers.

Main Select Query
SELECT * FROM
This part of the query selects the columns for the final result set.

Limitation of PIVOT Queries
PIVOT Queries has limitation. They aren’t dynamic. All the column headers need to be static or hard-coded. If the pivoted column has values that vary over time, then we need to modify the query each time the data in the pivoted column changes.

SQL PIVOT Queries can save our time and it is cost effective also. That’s why it is better to use it.