What is the best way to merge data from a database?

Question

MySQL

Good day!
There is an entity Translation (Entity).

@Entity @Data
@Table(name = "translations")
public class Translation {

    @Id  @GeneratedValue(strategy = GenerationType.AUTO)
    private Long translationId;

    @Column
    private String locale;

    @Lob
    @Column(name = "messagekey", length = 3000)
    private String key;

    @Lob
    @Column(name = "messagecontent", length = 100000)
    private String content;
}

The translations table itself:

+----------------+-------------------------+------------+--------+
| translation_id |     messagecontent      | messagekey | locale |
+----------------+-------------------------+------------+--------+
|              1 | текст на русском        | key1       | ru     |
|              2 | text in english         | key1       | en     |
|              3 | text auf Deutsch        | key1       | de     |
|              4 | другой текст на русском | key2       | ru     |
|              5 | other text in english   | key2       | en     |
|              6 | andere text auf Deutsch | key2       | de     |
+----------------+-------------------------+------------+--------+

The task is to combine the data in such a way as to ultimately get something like this structure:

+------------+-------------------------+-----------------------+-------------------------+
| messagekey |           ru            |          en           |           de            |
+------------+-------------------------+-----------------------+-------------------------+
| key1       | текст на русском        | text in english       | text auf Deutsch        |
| key2       | другой текст на русском | other text in english | andere text auf Deutsch |
+------------+-------------------------+-----------------------+-------------------------+

Simply put, you need to transfer from the vertical structure of the table to the horizontal one.
Accordingly, the question is:
At what level is it better to do this?

• At the MySQL level (is there a ready-made solution in the JPQL syntax or do I need to use nativeQuery). If yes, what exactly? GROUP_CONCAT, CONCAT, GROUP BY etc.
  
• At the level of Java code. Then the question is - how to iterate over 3 lists at the same time? Moreover, the length of the lists may vary.
  

Stream.concat(listA.stream(), listB.stream())allows you to iterate over 2 lists.
My current solution, though not the best...

List<Translation> translations_DE = translationService.findAllByLocale("de");
List<Translation> translations_RU = translationService.findAllByLocale("ru");
List<Translation> translations_EN = translationService.findAllByLocale("en");
List<TranslationDTO> translationDTOS = new ArrayList<>();
int len = translations_DE.size();
len = Math.max(len, translations_RU.size());
len = Math.max(len, translations_EN.size());
for (int i = 0; i < len; i++) {

    String translationKey = translations_DE.get(i).getKey();

    Long translationId_DE = translations_DE.get(i).getTranslationId();
    Long translationId_RU = translations_RU.get(i).getTranslationId();
    Long translationId_EN = translations_EN.get(i).getTranslationId();

    String translationContent_DE = translations_DE.get(i).getContent();
    String translationContent_RU = translations_RU.get(i).getContent();
    String translationContent_EN = translations_EN.get(i).getContent();

    TranslationDTO translationDTO = new TranslationDTO();
    translationDTO.setKey(translationKey);
    translationDTO.setId_DE(translationId_DE);
    translationDTO.setId_RU(translationId_RU);
    translationDTO.setId_EN(translationId_EN);
    translationDTO.setContent_DE(translationContent_DE);
    translationDTO.setContent_RU(translationContent_RU);
    translationDTO.setContent_EN(translationContent_EN);

    translationDTOS.add(translationDTO);
}

I would be glad for any hint! Thanks in advance)
I understand that this is a Pivot Table. But as far as I know, MySQL does not have it.

SELECT "messagekey",
       SUM("locale" = 'de') AS de,
       SUM("locale" = 'ru') AS ru,
       SUM("locale" = 'en') AS en
FROM "translations"
GROUP BY "messagekey";

Gives a similar result... Only instead of text from the messagecontent column it displays numbers (as I understand the sum)

+------------+----+----+----+
| messagekey | de | ru | en |
+------------+----+----+----+
| key1       |  1 |  1 |  1 |
| key2       |  1 |  1 |  1 |
+------------+----+----+----+

Answer 1

Here is my MySQL solution

SELECT "messagekey",
MAX(CASE WHEN ("locale"='de') THEN "messagecontent" ELSE null END ) AS de,
MAX(CASE WHEN ("locale"='ru') THEN "messagecontent" ELSE null END ) AS ru,
MAX(CASE WHEN ("locale"='en') THEN "messagecontent" ELSE null END ) AS en
FROM "translations" GROUP BY "messagekey";

Result:

+------------+--------------------------+---------------------------+-----------------------+
| messagekey |            de            |            ru             |          en           |
+------------+--------------------------+---------------------------+-----------------------+
| key1       | text auf Deutsch         | текст на русском          | text in english       |
| key2       | andere text auf Deutsch  | другой текст на русском   | other text in english |
+------------+--------------------------+---------------------------+-----------------------+