Extracting and sorting data from a column with the SELECT command

Question

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!

Can you share an example of the output you want from this data, just so the question is a bit clearer? — Mureinik, Jan 22 '23 at 17:24
Hi @Mureinik, I added an example of the output I would like to have. — N_user, Jan 22 '23 at 17:28

Mureinik · Answer 1 · 2023-01-22T18:03:15.077

2

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

edited Jan 22 '23 at 18:03

answered Jan 22 '23 at 17:31

Mureinik

297,002
52
306
350

Thank you @Mureinik, Running your query gives me the following error message: ```Msg 156, Level 15, State 1, Line 7 Incorrect Syntax near en.``` – N_user Jan 22 '23 at 17:47
@N_user oops... forgot the join clause.. – Mureinik Jan 22 '23 at 18:03

score 2 · Answer 2 · answered Jan 22 '23 at 17:32

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.

Extracting and sorting data from a column with the SELECT command

2 Answers2