Post reply

Debating over CASE vs Derived Tables for Converting Rows to Columns

  • April 12, 2006 at 9:09 am

    #113675

    I have seen a lot of posts come accross on converting rows of data to columns.  I run into this quite a bit, and always handle using Derived tables.  I do a lot of reporting of financial information and need to come up with datasets that have items such as sales figures trend accross columns like the following:

    Product         Jan         Feb        Mar

    A                 100.00     150.00   200.00

    B                  75.00       80.00     90.00

    The issue is always that the data resides in ledger-type transactions in rows like:

    Sales Order         Item           Qty     Price     Date

    12365                A                  1      50.00    1/1/2006

    12366                A                   1      50.00   1/5/2006

    When first tackling this I thought about using CASE, but found it extremely limited due to the inability to group/rollup numbers in some cases, so I turned to using Derived Tables like in the following example:

    Select

    IM.Item, ISYR1.SalesYR1, ISYR2.SALESYR2 

    from ItemMaster IM

    Inner Join

    (Select Item, ItemSales as SalesYR1 from SalesDetail where Date <= @param1 and Date >= @param2) ISYR1

    on IM.ITEM = ISYR1.ITEM

    Inner Join

    (Select Item, ItemSales as SalesYR1 from SalesDetail where Date <= @param3 and Date >= @param4) ISYR1

    on IM.ITEM = ISYR2.ITEM

    Question:

    Is there any disadvantages to using the dervied table approach over CASE in simple situations?  Does performance suffer using Dervied Tables?

    Any input or discussion would be greatly appreciated!  Thanks in advance!

  • April 12, 2006 at 9:57 am

    #632139

    Hi Darin,

    The case method is more efficient, and I'd normally recommend using that. If you need to group/rollup numbers, you can always use the select containing the cases as a derived table and do whatever you need to on that.

    If you can show us an example of where you couldn't do something using the case method, then maybe we can show you how.

    I hope that helps...

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • April 12, 2006 at 6:11 pm

    #632261

    Here are a couple of scripts for performing "pivot table" type queries as well.  They may simplify your life a bit, or give you inspiration for automating CASE statement creations via dynamic sql:

    http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=680&lngWId=5

    http://www.sqlteam.com/item.asp?ItemID=2955

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply