0

I have three tables (simplified version), words, translations, languages.

words

 id | key    |
 ------------|
 1  | sport  |
 2  | league |
 3  | accept |

languages

 id | name   |
 ------------|
 1  | English|
 2  | Italian|
 3  | German |

translations

 id | word_id | language_id | translation |
 -----------------------------------------|
 1  | 1       | 1           | Sport       |
 2  | 1       | 2           | Sport       |
 3  | 3       | 2           | Accettare

Is there a way, using mySql, to get this result:

 ----------------------------------------------|
 sport  |  Sport      | Sport       | Sport    |
 league |  Leaugue    | Liga        | Lega     |
 accept |  Accept     | Akzeptieren | Accettare|

I tried using group by and group_concat, but it's not really what I need. I need it separated by languages and grouped by key.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Ned
  • 3,961
  • 8
  • 31
  • 49
  • I'm not fully comfortable with that table design. Are words in English already? – jarlh Jan 20 '17 at 10:11
  • keys from words table are in English, as well language names in languages table. but in translations table, every language has its own translation. regarding desired view, first column is key from words table, the others are translatations of that word (in all languages). does it make more sense now? – Ned Jan 20 '17 at 10:14
  • That result doesn't look very useful to me. Are you sure it's what you want? – Strawberry Jan 20 '17 at 10:58
  • yes, I need it like this :) – Ned Jan 20 '17 at 11:35

1 Answers1

0

What you are looking for is transposing your rows to columns in a generic way. Maybe this helps: MySQL pivot row into dynamic number of columns

Community
  • 1
  • 1
Jan Zeiseweis
  • 3,718
  • 2
  • 17
  • 24