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

Our Random Articles

More Links