Posted on

Crosstabs cell-shifting

A crosstab query is a specific query used to create aggregate reports on two or more fields, it’s  a handy way to display summary information. At Open Query we have customers using that trick to display production schedules.

The summary table is generated from the database to extract the manufacturing date (mand), unit number (unitn), product area (pro_area), and ranking (rnk). Then we can start using that summary table to process our crosstab. The summary table looks like this:

mandunitnpro_arearnk
2009-12-15587-MWI2-PP49111
2009-12-15587-MWI2-PP50110
2009-12-16670-W1101111
2009-12-17670-W110217
2009-12-16670-W1103110
2009-12-16683-BR107-S2-01129
2009-12-18683-BR107-S4-05a1211
2009-12-18683-BR107-S5-061210
2009-12-15691-02-10519
2009-12-16691-02-10718
2009-12-17691-03-10116
2009-12-17691-03-10515

Starting from that table we are going to generate the final report, who will display the production schedule by factory and by production order (rank).  Our crosstab query will look like this:

select
pro_area as ‘factory’,
max( case when mand = ‘2009-12-15′ then unitn else ” end ) as ’15Dec09’,
max( case when mand = ‘2009-12-16′ then unitn else ” end ) as ’16Dec09’,
max( case when mand = ‘2009-12-17′ then unitn else ” end ) as ’17Dec09’,
max( case when mand = ‘2009-12-18′ then unitn else ” end ) as ’18Dec09’
from pro_sched_sorted
group by pro_area, rnk
order by pro_area, rnk desc

SELECT pro_area as ‘factory’,

max( case when mand = ‘2009-12-15′ then unitn else ” end ) as ’15Dec09’,

max( case when mand = ‘2009-12-16′ then unitn else ” end ) as ’16Dec09’,

max( case when mand = ‘2009-12-17′ then unitn else ” end ) as ’17Dec09’,

max( case when mand = ‘2009-12-18′ then unitn else ” end ) as ’18Dec09’

FROM pro_sched_sorted

GROUP BY pro_area, rnk

ORDER BY pro_area, rnk desc

And the result of our query will give the following report:

factory15Dec0916Dec0917Dec0918Dec09
1587-MWI2-PP49670-W1101
1587-MWI2-PP50670-W1103
1691-02-105691-03-106
1691-02-107691-04-103
1670-W1102
1691-03-101
1691-03-105
5698-078
5698-079
5698-080
5698-081
5698-082698-087
5698-083698-088
5698-084698-089
5698-085698-090
5698-086698-091
9692-1930-073692-1930-081
9692-1930-074692-1930-082
9692-1930-075692-1930-083
9692-1930-076692-1930-084
10692-1930-077692-1930-085
10692-1930-078692-1930-086
10692-1930-079692-1930-087
10692-1930-080692-1930-088
12683-BR107-S4-05a
12683-BR107-S5-06
12683-BR107-S2-01

Now the result of our crosstab query is statistically correct, but the result layout is not satisfying because of the empty cells that are generated in the case a given factory does not have a production order for a specific date.

I have been looking into a solution for removing those empty cells on the server side. That would involve either filtering, or doing cell-shifting in SQL.

The filtering solution is not really an option since the date columns are aggregated. You cannot just add a WHERE … != ”clause because of the GROUP BY process.

For the second solution, I have thought of writing a special stored procedure as a cursor on the result set that would reorder the report by replacing empty cells by the value in the next row. Now probably MySQL stored procedure language would be somewhat limited for doing previous row/next row operations.

Any thoughts on doing cell-shifting in SQL?

2 thoughts on “Crosstabs cell-shifting

  1. Hi Guillaume,

    personally, I don’t think SQL is the right tool for this job. There
    are excellent reporting tools that can fire off a single efficient
    query and then organize the data on the client side to show all kinds
    of crosstabs. Personally, I favor Pentaho for this type of job but
    there are many more.

    Anyway, lets assume you still want to solve this in SQL.

    You say: ” the result layout is not satisfying because of the empty
    cells that are generated in the case a given factory does not have a
    production order for a specific date.” Well, in the result you show
    here, there are many empty cells, but there is never one entire row or
    one entire column with empty cells. In other words, I don’t see how
    you can remove anything without losing information.

    Let’s consider what it would take to get either an empty row or an empty column:

    emtpy row: you group by on pro_area, rnk, so any pro_area, rnk
    combination that does not have one of the dates you test for each
    column heading would result in an empty row. Since the column headings
    (i.e. the dates) are known in advance (you couldn’t have written your
    query otherwise) you can and in fact should restrict the results to
    those dates. And you should do so in the WHERE clause:

    WHERE mand IN (‘2009-12-15′,‘2009-12-16′,‘2009-12-17′,‘2009-12-18′)

    empty column: in this case it doesn’t matter much if you can detect it
    – the real problem is that you have the expression that generates the
    column already in the query. Fortunately,the fix is easy – don’t
    select that column 🙂

    kind regards,

    Roland

  2. I don’t understand, what’s the desired result set?

    If the answer appears to be “use WHERE, but after the GROUP BY”, that’s what HAVING is for.

Comments are closed.