Michael Frey Michael Frey - 4 months ago 16
SQL Question

Transform on MySql subquery

I want to mysql join 2 tables in a special way.

Here are my 2 tables:

Rights


right_id

thumbnail

enabled


1
img1.png
1


2
img2.png
1


3
img3.png
1




Right Strings


right_id

language

string_key

string value


1

en

short_header

Right 1


1

de

short_header

Recht 1


1

de

long_header

Langer Text


1

en

long_header

Long Text


1

en

content

Bla Bla Bla


1

de

content

Bla Bla Bla




This repeats for other languages and all rights as well.

Is it possible to create a query that will return all rights as only one row in the following format?


right_id
thumbnail
enabled
short_header_en
short_header_de
long_header_en
long_header_de
content_en
content_de


1
img1.png
1
Right 1
Recht 1
Long Text
Langer Text
Bla Bla Bla
Bla Bla Bla



I assume that mysql has some kind of transform for subqueries but I do not know what to even look for.

Thank you

Answer

This seems to be a table pivot issue, not sure if it's what you exactly want or not, but try it.

SELECT
    r.right_id, r.thumbnail, r.enabled,
    MAX(CASE WHEN CONCAT(rs.string_key, '_', rs.`language`) = 'short_header_en' THEN string_value END) AS `short_header_en`,
    MAX(CASE WHEN CONCAT(rs.string_key, '_', rs.`language`) = 'short_header_de' THEN string_value END) AS `short_header_de`,
    MAX(CASE WHEN CONCAT(rs.string_key, '_', rs.`language`) = 'long_header_en' THEN string_value END) AS `long_header_en`,
    MAX(CASE WHEN CONCAT(rs.string_key, '_', rs.`language`) = 'long_header_de' THEN string_value END) AS `long_header_de`,
    MAX(CASE WHEN CONCAT(rs.string_key, '_', rs.`language`) = 'content_en' THEN string_value END) AS `content_en`,
    MAX(CASE WHEN CONCAT(rs.string_key, '_', rs.`language`) = 'content_de' THEN string_value END) AS `content_de`
FROM rights r
INNER JOIN right_strings rs
ON r.right_id = rs.right_id
GROUP BY r.right_id

For dynamic table pivot:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN CONCAT(rs.string_key, '_', rs.`language`) = ''',
      string_key, '_', `language`
      ''' THEN rs.string_value END) AS ',
      string_key, '_', `language`
    )
  ) INTO @sql
FROM right_strings;

SET @sql = CONCAT(
    'SELECT r.right_id, r.thumbnail, r.enabled, ',
    @sql,
    ' FROM rights r
    INNER JOIN right_strings rs
    ON r.right_id = rs.right_id
    GROUP BY r.right_id;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Comments