CODEWITHSUNDEEP
sqlselect
Napkin
Napkin

Reputation: 19

Create SQL SELECT Statement to 'modify' small table

I am pretty new to SQL in general and want to 'modify' the following table (Tablename: translations / PK: id, language, key):

| id | language | key        | value        |
---------------------------------------------
| 1  | DE       | #Keyboard  | Tastatur     |   
| 1  | EN       | #Keyboard  | Keyboard     |   
| 1  | ES       | #Keyboard  | Teclado      |    
| 2  | DE       | #Screen    | Bildschirm   |  
| 2  | EN       | #Screen    | Screen       | 
| 3  | ES       | #Equipment | Equipo       |

I want to get a table like this:

| key        | valueDE      | valueEN      |
--------------------------------------------
| #Keyboard  | Tastatur     | Keyboard     | 
| #Screen    | Bildschirm   | Screen       |
| #Equipment | null         | null         |

So basically I want the translation of the values for the languages DE and EN next to each other in columns. Entries with no translation for DE and EN should have an empty field in the column valueDE and valueEN or if only one translation is available the other field in the column should be empty as shown in the table above.

Help would be appreciated a lot.

Upvotes: 0

Views: 37

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

Here it is, your definition re-worded into SQL.

select key,
  (select value from translations where language='DE' and key=t.key limit 1) "valueDE",
  (select value from translations where language='EN' and key=t.key limit 1) "valueEN"
from (select distinct key from translations) t;
key valueDE valueEN
#Keyboard Tastatur Keyboard
#Equipment
#Screen Bildschirm Screen

Upvotes: 1

Related Questions