Question 21136 in Mysql Question posted in
The official MySQL documentation can be found here.

Extracting and sorting data from a column with the SELECT command – Mysql

I have a table with codes for multilingual commands with a structure like this:

id code lang text
1 exit_command de Fenster Schliessen
2 exit_command en Close window

….
(The column lang also contains other languages such as Italian, Japanese, etc.)

I want to extract all texts for German and English from that table sorting them by code.
Running the command below, I get a mix of German and English in the same column:

SELECT * FROM table WHERE (lang='de' or lang='en')

But I would like to have a column for lang=English with English Text in a separate column and a column for lang=German with German Text in a separate column.

The structure of the output should look like this:

code lang German text lang English text
exit_command de Fenster schliessen en Close window

…

How can I get this output with an SQL query? Thank you for your help!

Tags:

2

Answers


  1. 0

    I’d join two subqueries on the table by their code:

    SELECT de.code, de.lang, de.text, en.lang, en.text
FROM   (SELECT code, lang, text
        FROM   mytable
        WHERE  lang = 'de') de
JOIN   (SELECT code, lang, text
        FROM   mytable
        WHERE  lang = 'en') en ON de.code = en.code
    Login or Signup to reply.
  2. 0

    This problem falls into the pivot task:

    • The CASE expression will help you extract the values you need.
    • The MAX aggregation will allow you to remove the unneeded NULL values, with respect to the partition (GROUP BY code_).
    SELECT code_,
       'de' AS lang1,
       MAX(CASE WHEN lang = 'de' THEN text_ END) AS GermanText,
       'en' AS lang2,
       MAX(CASE WHEN lang = 'en' THEN text_ END) AS EnglishText
FROM tab
GROUP BY code_

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search