Table transposition in SQL

Here’s a cool SQL trick for doing a partial transposition of a table:

select id,

  max(case when language_id = 1 then text else null end) pl_text,

  max(case when language_id = 2 then text else null end) en_text

from translations

group by id;

This is useful if you have some data of (logically) one entity spread over several physical records in table. In the above example we have translations table that contains data like this:

id	language_id	text
1	1	pies
1	2	dog
2	1	kot
2	2	cat

This is one of two most popular solutions for storing translations (or other similar data) in relational database. The other one is to have separate column for each language, like this:

id	pl_text	en_text
1	pies	dog
2	kot	cat

In my opinion both solutions have pros and cons and none is much better than the other (comments welcome if your opinion is different). In the first representation you don’t have to modify the schema if you want to add a new language, but getting values for all the languages at once is more complicated. In the second version, spotting missing translations is easier, but additional care must be taken to avoid overwriting changes if two translators would be working on the same record.

That’s when the above SQL snippet comes in handy. If you decided to stick with the first representation, you can use it to convert it on-the-fly to the other one. You can even define a view with this query, but if you do it so often that you need a view, maybe you should consider converting to the second representation?

How does it work?

When I first saw it, my reaction was: it can’t work! But then I analyzed it a little bit and the idea dawned on me and it was good. And pleasant. So I won’t spoil the pleasure for you — if you know how grouping in SQL works you should have no problem understanding it. Feel free to explain it in the comments, though.