Crosstab queries
I was working on ResourceBlender last week trying to find a way to get the data from multiple rows in SQL. I thought I’d have to loop through programatically and abuse Dictionaries and HashTables until I found out about a lesser known and at first confusing feature of SQL known as cross tabulation.
The data I needed contained translations. I have a table for elements and a table for translations, and each translation can be used by multiple elements.
mysql> SELECT elementname, LANGUAGE, translation, elements.translationid FROM elements INNER JOIN translations ON translations.translationid = elements.translationid WHERE elements.translationid BETWEEN 24 AND 25; +-------------+----------+-------------------+---------------+ | elementname | LANGUAGE | translation | translationid | +-------------+----------+-------------------+---------------+ | PrevWeek | de-DE | Vorherige Woche | 24 | | PrevWeek | en-GB | Previous Week | 24 | | PrevWeek | es-ES | Semana anterior | 24 | | PrevWeek | nl-NL | Vorige week | 24 | | PrevWeek | ro-RO | Saptamana trecuta | 24 | | PrevMonth | cs-CZ | Předchozí rok | 25 | | PrevMonth | de-DE | Vorheriger Monat | 25 | | PrevMonth | el-GR | Προηγ. Ετος| 25 | | PrevMonth | en-GB | Previous Month | 25 | | PrevMonth | es-ES | Año anterior | 25 | | PrevMonth | fr-FR | Préc. An | 25 | | PrevMonth | it-IT | Anno prec. | 25 | | PrevMonth | nl-NL | Vorig jaar | 25 | | PrevMonth | pl-PL | Poprzedni rok | 25 | | PrevMonth | pt-PT | Ano anterior | 25 | | PrevMonth | ro-RO | Luna trecuta | 25 | | PrevMonth | ru-RU | Предыдущий год| 25 | | PrevMonth | sl-SI | Prejšnje leto | 25 | | PrevMonth | sv-SE | Föreg. År | 25 | | PrevMonth | tr-TR | րnceki Yýl | 25 | | PrevMonth | zh-Hans | 上一年 | 25 | +-------------+----------+-------------------+---------------+ 21 rows IN SET
Each translation is grouped logically by the translationid, so I really needed one row for each language and columns for each language.
This would be simpler if there were a standard set of languages used by every application in the database, but there can be any combination of the user’s choice which makes it slightly more difficult. I’m actually storing the languages used for each application in a separate table so I know which columns to expect.
The syntax for the final query is below:
mysql> SELECT elementname, LANGUAGE, elements.translationid, MAX(IF(LANGUAGE='cs-CZ',translation,NULL)) AS 'cs-CZ', MAX(IF(LANGUAGE='de-DE',translation,NULL)) AS 'de-DE', MAX(IF(LANGUAGE='el-GR',translation,NULL)) AS 'el-GR', MAX(IF(LANGUAGE='en-GB',translation,NULL)) AS 'en-GB', MAX(IF(LANGUAGE='es-ES',translation,NULL)) AS 'es-ES', MAX(IF(LANGUAGE='fr-FR',translation,NULL)) AS 'fr-FR', MAX(IF(LANGUAGE='it-IT',translation,NULL)) AS 'it-IT', MAX(IF(LANGUAGE='nl-NL',translation,NULL)) AS 'nl-NL', MAX(IF(LANGUAGE='pl-PL',translation,NULL)) AS 'pl-PL', MAX(IF(LANGUAGE='pt-PT',translation,NULL)) AS 'pt-PT', MAX(IF(LANGUAGE='ro-RO',translation,NULL)) AS 'ro-RO', MAX(IF(LANGUAGE='ru-RU',translation,NULL)) AS 'ru-RU', MAX(IF(LANGUAGE='sl-SI',translation,NULL)) AS 'sl-SI', MAX(IF(LANGUAGE='sv-SE',translation,NULL)) AS 'sv-SE', MAX(IF(LANGUAGE='tr-TR',translation,NULL)) AS 'tr-TR', MAX(IF(LANGUAGE='zh-Hans',translation,NULL)) AS 'zh-Hans' FROM elements INNER JOIN translations ON translations.translationid = elements.translationid WHERE elements.translationid BETWEEN 24 AND 25 GROUP BY elements.translationid; +-------------+----------+---------------+---------------+------------------+-------------+----------------+-----------------+----------+------------+-------------+---------------+--------------+-------------------+----------------+---------------+-----------+------------+---------+ | elementname | LANGUAGE | translationid | cs-CZ | de-DE | el-GR | en-GB | es-ES | fr-FR | it-IT | nl-NL | pl-PL | pt-PT | ro-RO | ru-RU | sl-SI | sv-SE | tr-TR | zh-Hans | +-------------+----------+---------------+---------------+------------------+-------------+----------------+-----------------+----------+------------+-------------+---------------+--------------+-------------------+----------------+---------------+-----------+------------+---------+ | PrevWeek | de-DE | 24 | NULL | Vorherige Woche | NULL | Previous Week | Semana anterior | NULL | NULL | Vorige week | NULL | NULL | Saptamana trecuta | NULL | NULL | NULL | NULL | NULL | | PrevMonth | cs-CZ | 25 | Předchozí rok | Vorheriger Monat | Προηγ. Ετος | Previous Month | Año anterior | Préc. An | Anno prec. | Vorig jaar | Poprzedni rok | Ano anterior | Luna trecuta | Предыдущий год | Prejšnje leto | Föreg. År | րnceki Yýl | 上一年 | +-------------+----------+---------------+---------------+------------------+-------------+----------------+-----------------+----------+------------+-------------+---------------+--------------+-------------------+----------------+---------------+-----------+------------+---------+ 2 rows IN SET
This magical query will first group by rows with a common translationid, then show each language as it’s own row. If no translation for a certain translationid in a certain language exists, a null is used.
With crosstab queries it’s also possible to use use the data in the table to create your columns and to use SQL functions for the values of columns, this lets you do thing like keep running totals and averages.
The following links are good resources for more information on crosstab queries:
[...] control for displaying and editing tabular data, but if you try to make it work with MySQL or a cross-tab query you’re likely to run into [...]