Mysql: Cross tabulation: Very useful article from mysql tech-resources

MySQL :: MySQL Wizardry

Cross tabulations are statistical reports where you de-normalize your data and show results grouped by one field, having one column for each distinct value of a second field.



Basic problem definition. Starting from a list of values, we want to group them by field A and create a column for each distinct value of field B.



The desired result is a table with one column for field A, several columns for each value of field B, and a total column.

Mysql: Oracle users looking for Rownum in mysql !!

Sadly, MySQL doesn't have (yet) the ROWNUM function. But a simple playing around with variables will give you the desired result.

Here is an example: (Src: dzone)
mysql code

SELECT @rownum := @rownum + 1 as rownum, t.* FROM some_table t, (SELECT @rownum := 0) r