Skip to content

MS SQL

SQL Server Pivot – A Simple and Practical Example

SQL Server Pivot was introduced in SQL Server 2005. It is a useful feature but it’s syntax and implementations sounds a bit complex. Let us try to understand this by a simple example.

Problem Scenario

You are working for software project for a client who has 5 stores located in say 5 different cities. Each store have their own POS (point of sales) software and the sales data is uploaded after a regular interval to a centralized server. The centralized database server table has records like the following:

SaleId

StoreName

Sales

SaleDate

1001

Store-1

5000

Jun-12-2014

1002

Store-2

4500

Jun-16-2014

1003

Store-5

7800

Jun-19-2014

1004

Store-3

9000

Jun-22-2014

1005

Store-4

3900

Jun-29-2014

1006

Store-2

2800

Jul-11-2014

1007

Store-1

3100

Jul-15-2014

         

Now, based on above table, if I your client asks you to find the monthly performance of each of the stores, it would be quite easier for you cause the table has very few records. You can do it manually and may share it in an spreadsheet. What if the above table has records from last one year? What if there are 100s of stores? What if the client requires this summary report to be automated and generated on the fly? In real application, the table may have millions of records, a complex calculation, indeed!

Solution

Pivot was introduced to easily sort out these kind of scenarios – i.e. Summary Reports. Pivot syntax might look a bit confusing for the beginners. The best way to learn this would be to follow this tutorial and work it out. Once you’ve a little working understanding of the example shared here, visualize your own scenario, create your own table and implement your solution based on the template provided here.

Step# 1

Create the table using following query. OR if you are in a hurry, skip this and directly go to Step# 2 for a ready made script that can be executed to CREATE and INSERT the sample records.

CREATE TABLE [dbo].[StoreSales](
[SaleId] [int] IDENTITY(1,1) NOT NULL,
[StoreName] [varchar](30) NOT NULL,
[Sales] [decimal](10, 2) NOT NULL,
[SaleDate] NOT NULL,
CONSTRAINT [PK_StoreSales] PRIMARY KEY CLUSTERED
(
[SaleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,        IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Step# 2

You can DOWNLOAD and EXECUTE the following script to CREATE and INSERT sample records required for this example.


Step# 3

Execute the following PIVOT script. Or COPY it from here Pivot_Script

Pivot

 

 

 

 

 

In the sample table we’ve inserted records starting from July-2013 to July-2014. That’s the reason Jan-2013 to June-2013 is showing NULL values. You can play around with it by tweaking a little to implement the same template for many other practical scenarios. See the final result here [Click the image for a complete BIGGER picture]:

Click me for a BIGGER image

Click me for a BIGGER image

If you want to explore more there’s a reverse case of PIVOT and that’s known as UNPIVOT. Google about it.

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)