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.