Hamed Hamed - 4 months ago 12
SQL Question

MySQL pivot table for Multilingual Dictionary

My table structure looks like the following:

enter image description here

As you can see, its a multilingual dictionary that all the values for each language are stored on the same table, repeating the 'key' value.

What I want is a SQL statement to fetch all the rows with unique 'key', including the 'value' columuns for each language.

What I tried until now, it works but only for two languages, no more:

select a.*, b.value
from `translator_messages` a
JOIN `translator_messages` b
on a.key = b.key and
a.lang !=b.lang
group by a.key


And the result:

As you can see, it only list the values for first two languages, not more.

Note: its better to have a generic SQL solution, not relying on MySQL specific features.

Answer

You need dynamic pivot table to accomplish this.

SET @sql := NULL;

SELECT
    GROUP_CONCAT(t.output) INTO @sql
FROM
    (
        SELECT
            CONCAT(
                'MAX(CASE WHEN lang=\'',
                lang,
                '\' THEN `value` END) AS ',
                CONCAT(REPLACE (lang, '-', '_'),'_value')
            ) output
        FROM
            translator_messages
        GROUP BY
            lang
    ) AS t;


SET @SQL := CONCAT(
    'SELECT `key`,category,' ,@SQL,
    ' FROM translator_messages GROUP BY `key`'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

WORKING DEMO

Sample input:

| id |  lang | category |   key | value |
|----|-------|----------|-------|-------|
|  1 | en-US |      app |  book |    v1 |
|  2 | fa-IR |      app |  book |    v2 |
|  3 | de-GE |      app |  book |    v3 |
|  4 | en-US |      app | salad |    v4 |
|  5 | fa-IR |      app | salad |    v5 |
|  6 | de-GE |      app | salad |    v6 |

Sample Output(genrated by the above query):

|   key | category | de_GE_value | en_US_value | fa_IR_value |
|-------|----------|-------------|-------------|-------------|
|  book |      app |          v3 |          v1 |          v2 |
| salad |      app |          v6 |          v4 |          v5 |

Caution:

Beware of MySQL max size for a string variable and GROUP_CONCAT.

If GROUP_CONCAT max length is the limit (1024 by default) you should alter the temporary setting (session-scope) for length of it. It's done by:

SET SESSION group_concat_max_len = 10000

Set group_concat_max_len permanently (MySQL config)