Monday, October 16, 2006

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
ATM02 APP 250
ATM02 DEC 23

Listing 1

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

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:

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

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:

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.


BuZain said...

A neat trick. Microsoft introduced a "PIVOT" command on SQL 2005 but I haven't used it yet.

BTW, I think your queries are missing "GROUP BY".

Ayman said...

Thanks for the GROUP BY catch. I modified the post.
Microsoft SQL Server has a slightly more difficult syntax with IF's.

iq8bit said...

thank you!!!!!!!!

Just Google it!