Pages

Friday, July 29, 2016

Basic of PIVOT:Why and When to Use PIVOT function in SQL?

 One of the most powerful feature in Excel is Pivot, the folk who work with BI, heavily uses PIVOT table in Excel. However, MS SQL and Oracle has function called PIVOT that can transpose your rows into column. Though, Teradata doesn’t have the PIVOT functionality but you can achieve the by using many lines of SQL code.

Let’s start with an example, say my below table holds information about my family expenses:

Shop
Prod_Type
Price
Fotex
Grocery
560.00
Fotex
Medicine
300.00
Fotex
Electronics
2000.00
El-giganten
Electronics
5600.00
Netto
Grocery
420.00
Netto
Medicine
250.00



     Fig 1: Family expenses data

If you look at the data above you will find there are three different types of product e.g. Grocery, Electronics and Medicine.

However, I would like to see the data in different way which can give much clear view in terms of product type I regularly buy e.g.

Shop
Grocery
Electronics
Medicine
El-giganten
NULL
5600.00
NULL
Fotex
560.00
2000.00
300.00
Netto
420.00
NULL
250.00

     Fig 2: Transposed view (rows turned into column)

By looking at the above data, I can easily see different sector of my family expenses.  To get the above data view I had to transpose rows into the columns. And this can be accomplished by using PIVOT function in MS SQL.

Now, the fun part, SQL Coding:

Step1: Create a temporary table

CREATE TABLE #tempFamilyExpense
(
Shop varchar(15),
Prod_Type varchar(20),
Price decimal(10,2)
)


Step 2. Insert some dummy data

INSERT INTO #tempFamilyExpense VALUES ('Fotex','Grocery',560.00)
INSERT INTO #tempFamilyExpense VALUES ('Fotex','Medicine',300.00)
INSERT INTO #tempFamilyExpense VALUES ('Fotex','Electronics',2000.00)


INSERT INTO #tempFamilyExpense VALUES ('El-giganten','Electronics',5600.00)


INSERT INTO #tempFamilyExpense VALUES ('Netto','Grocery',420.00)
INSERT INTO #tempFamilyExpense VALUES ('Netto','Medicine',250.00)

Step 3. Look at the current data and then make the view as figure 2 by using PIVOT
/** Show original table **/
SELECT * FROM  #tempFamilyExpense

** Create transpose using PIVOT **/
SELECT *
FROM #tempFamilyExpense
PIVOT
 (
 SUM(Price)
 FOR Prod_Type
 IN (Grocery,Electronics,Medicine)
 )

 AS pv


You will find many cases where you have to make columns by using the row data (columns value) then PIVOT is your friend to help with that.