Q

Rows to columns (another "crosstab" report)

This Content Component encountered an error

I have two tables. The first table contains Sales data with the following fields:

 ItemKey QtySold A 6 B 2 C 1 A 2 A 1

The second table contains some OH data with the following fields:

 ItemKey MOHClass Amt A Fixed .13 A Var .06 A Cap .03

What I need the output to look like is a summation by product code for each of the sales.

 ItemKey Qty MOHFixed MOHVar MOHCap A 9 .13 .06 .03 B 2 .00 .00 .00 etc.

How can this be done? Thanks for your help.


We've answered questions like this before. It's called either "denormalizing" or, alternatively, creating a "crosstab" result. The solution requires CASE expressions in the SELECT clause, each of which tests for a specific row value, along with SUM() functions and a GROUP BY clause:

 select Sales.ItemKey , sum(Sales.QtySold) as Qty , sum( case when OH.MOHClass = 'Fixed' then OH.Amt else .00 end ) as MOHFixed , sum( case when OH.MOHClass = 'Var' then OH.Amt else .00 end ) as MOHVar , sum( case when OH.MOHClass = 'Cap' then OH.Amt else .00 end ) as MOHCap from Sales left outer join OH on Sales.ItemKey = OH.ItemKey group by Sales.ItemKey

Note the use of the LEFT OUTER JOIN to ensure we get result rows for Sales which don't have OH rows. If OH means "on hand" this might be required if the application maintains only current items in its OnHand table, rather than a bunch of rows with zeros.


This was first published in March 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close