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.
