Nick Harrison

"Flattening" a "deep" table

Have you ever been struck with a table design that did not work well for what you now needed?    Sure, we all have.

One common scenario is to have a table where all of the data that you need is in multiple records, but you need it returned in a single record.

A common solution is to join the table to itself multiple times.    Depending on the size of the table, this might work.   Sadly this approach does not scale well.

A more scalable solution would be to use grouping logic to get all of the data in a single pass.

Suppose you have a table that totals sales data monthly, but you need to display multiple months side by side.

Here is the table

If you wanted to produce a record set that looks like this:

You could do something like this:

select JuneData.Volume as June, JulyData.Volume as July, 
       AugustData.Volume as August, 
       SeptemberData as September,
       OctoberData.Volume as October
from   SalesData JuneData, 
       SalesData JulyData, 
       SalesData AugustData, 
       SalesData SeptemberData, 
       SalesData OctoberData
where  JuneData.ReportingDate = '06/01/05' and 
       JulyData.ReportingDate = '07/01/05' and
       AugustData.ReportingDate = '08/01/05' and
       SeptemberData.ReportingDate = '09/01/05' and
       OctoberData.ReportingDate = '10/01/05'

This works well for small tables, but if the table in question is large, this doesn't work so good.   Instead I prefer to do something similar to this:

select max (case reportingdate when '06/01/05' then volume 
           else null end) as June,
       max (case reportingdate when '07/01/05' then volume 
           else null end) as July,
       max (case reportingdate when '08/01/05' then volume 
           else null end) as August,
       max (case reportingdate when '09/01/05' then volume 
           else null end) as September,
       max (case reportingdate when '10/01/05' then volume 
           else null end) as October
from   SalesData

You could then add any additional filters you need like region restrictions, product restrictions, etc.

You get the same results as the earlier query, but with better scalability.

Once you get comfortable with this pattern, you'll be surprised at how often you can use it.

posted @ Thursday, November 24, 2005 7:09 PM | Feedback (1)