MySQL pivot table for Multilingual Dictionary

Question

My table structure looks like the following:

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:

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

That looks like the result your current query might generate, what would be helpful instead of that is your desired output based on the sample data. — Hart CO, Jul 20 '16 at 15:39
as you can see, the results only include the 'value' columns for just two languages. Based on the sample data, the value column for 'de-GE' language is missing. Maybe more languages included. — Rev, Jul 20 '16 at 15:44
It's just unclear what logic you're using to get those results. You want it to pick one of the language records arbitrarily, and two of the `value`'s arbitrarily? — Hart CO, Jul 20 '16 at 16:36

score 1 · Answer 1 · answered Jul 20 '16 at 15:26

1

If you want keys that appear only once, then you can use not exists:

select tm.*
from translator_messages tm
where not exists (select 1
                  from translator_messages tm2
                  where tm2.key = tm.key and tm2.id <> tm.id
                 );

Or, if you simply want all the values in a single column for each key:

select tm.key, group_concat(value separator '|') as values
from translator_messages tm;

This puts all the values in a single column. You can even specify the language if you like:

select tm.key, group_concat(lang, ':', value separator '|') as values
from translator_messages tm;

answered Jul 20 '16 at 15:26

Gordon Linoff

1,242,037
58
646
786

Mysql returns an empty result set! – Rev Jul 20 '16 at 15:37
@Hamed The first query returns an empty set because none of your keys only appear once. I'm guessing you're interested in the `GROUP_CONCAT()` options. – Hart CO Jul 20 '16 at 15:41
not-concatenated values are required. separate values are required for the application. – Rev Jul 20 '16 at 15:46

score 1 · Answer 2 · answered Jul 20 '16 at 15:52

1

Don't use GROUP BY a.key, since that just shows one row for each key, not all the translations. If you want to see all the translations for the same key together, but in separate rows, use ORDER BY a.key.

You may also want to change a.lang != b.lang to a.lang < b.lang. The current query will show the same pair of rows twice, once for a.lang = en-US and b.lang = fa-IR and then again for a.lang = fa-IR and b.lang = en-US. Using < instead of ! makes it show just the first pair.

answered Jul 20 '16 at 15:52

Barmar

741,623
53
500
612

By replacing != with < it still brings repetitive rows – Rev Jul 20 '16 at 18:16
What repetitions? Edit your question and show the output you're trying to get. – Barmar Jul 20 '16 at 19:10

score 0 · Accepted Answer · edited Apr 13 '17 at 12:13

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)

Wow, finally a working solution. Thanks. I never imagined it could become such a complex one! first time using mysql statements and studying... — Rev, Jul 20 '16 at 18:19

MySQL pivot table for Multilingual Dictionary

3 Answers3