MySQL Crosstabs

I was writing some reports, and needed a better way of visualising some data. Instead of of having something like:

Term     Resp    Count
ATM01    APP    100
ATM01    DEC    10
ATM01    REV    5
ATM02    APP    250
ATM02    DEC    23

Listing 1

something like the following is much easier to look at, and graph:

Term     APP    DEC    REV
ATM01    100    10     5
ATM02    250    23     null 

Listing 2

There is no built-in crosstab or pivot construct in MySQL, but this is much easier than it seems. After you create it once, it will become second nature. I am using it very frequently to create all sorts of statistics and graphs.

So, let us assume you have the above data in a table like Listing 1. You need to transform it, or create another query that will generate to you the result in Listing 2.

Here is the Query:

SELECT Term,
     SUM( IF(Resp = 'APP', 1, null) ) as APP,
     SUM( IF(Resp = 'DEC', 1, null) ) as DEC,
     SUM( IF(Resp = 'REV', 1, null) ) as REV
 FROM mytable
GROUP BY Term

The trick is using a SUM of 1 or null. 1 will be added when the condition in the IF applies, and null otherwise. The beauty of this, is that you can have any arbitrary complex expression in the IF statement to group records that must be added in the APP column. For example let us say you need to have only two columns for APP and OTHERS. Your query will be:

SELECT Term,
     SUM( IF(Resp = 'APP', 1, null) ) as APP,
     SUM( IF(Resp <> 'DEC', 1, null) ) as OTHERS
 FROM mytable

This gives you much more flexibility in constructing your crosstabs. It May get a bit complicated if you need many columns, or if you need a single column per value. If you have this, then probably the best thing is use the right tool for this. Use Microsoft Excel. Or use any one of several techniques that build the SQL for you. Just do a Google search on MySQL crosstab, and you will find some good ones. One good article on MySQL site is here.