Rows to columns (another "crosstab" report)

Rows to columns (another "crosstab" report)

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.


    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top