ReadVitamin

As a new comer to Teradata from Oracle, I had always wanted do something unusual like displaying rows as columns which is also called as crosstab report matrix or pivot queries. Let’s see how this is done. Say I have two tables one is having sales data and the other is having pricing information.

Sales Table

Item_Name Unit_Sold
Tea           10
Coffee       25
Notebook   5
iPhone       20
Apple        50

Price Table

Item_Name  Location Unit_Price
Coffee        East       5.00
Coffee        West      5.00
iPhone        West      600.00
iPhone        North      600.00
iPhone        South     500.00
Tea           North      6.00
Tea           South     6.00
Notebook   North      550.00
Apple        West      2.00
Apple        South      2.00
Apple        North      2.00

And we need a report that shows area wise sales report, something like the following.

Item Sold	Unit_Sold	East	West	South	North
Tea	20	0	0	6	6
Notebook	5	0	0	0	550
Coffee	50	5	5	0	0
iPhone	60	0	600	600	500
Apple	150	0	2	2	2

By the usage of CASE expressions within a SUM function in the select clause which is similar in Oracle, we can achieve the desired output. Here is the code, do not forget to use Group By clause.

SELECT sales.Item_Name AS “Item Sold”,
SUM (Sales.unit_sold) AS Unit_Sold,
price.unit_price as “Unit Price”,
SUM (case when price.location = ‘East’ THEN price.unit_price*Sales.unit_sold else 0 END)
AS “Sales in East” ,
SUM (case when price.location = ‘West’ THEN price.unit_price*Sales.unit_sold else 0 END)
AS “Sales in West”,
SUM (case when price.location = ‘South’ THEN price.unit_price*Sales.unit_sold else 0 END)
AS “Sales in South”,
SUM (case when price.location = ‘North’ THEN price.unit_price*Sales.unit_sold else 0 END)
AS “Sales in North”
FROM sales, price
WHERE sales.item_name = price.item_name
GROUP BY sales.item_name, price.unit_price

Popularity: 10% [?]