infomf infomf - 1 month ago 10
MySQL Question

MySQL Multiple Query > storing rows into columns

I need to create a SQL query that lists the following tables. Lines from the language list as column names. Thanks

Picture table and query:

enter image description here

Answer

If languages are known upfront

SELECT r.id, r.parent,
       MAX(CASE WHEN n.language_id = 1 THEN n.name END) cs,
       MAX(CASE WHEN n.language_id = 2 THEN n.name END) en
  FROM cat_route r LEFT JOIN cat_route_name n
    ON r.id = n.cat_route_id
 GROUP BY r.id

Here is SQLFiddle demo


If you want it to be dynamic depending on what languages you defined in language

SET @sql = NULL;

SELECT GROUP_CONCAT(CONCAT(
  'MAX(CASE WHEN n.language_id = ', id, ' THEN n.name END) `', short, '`'))
  INTO @sql
  FROM language;

SET @sql = CONCAT(
  'SELECT r.id, r.parent, ', @sql, '
     FROM cat_route r LEFT JOIN cat_route_name n
       ON r.id = n.cat_route_id
    GROUP BY r.id');

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

Here is SQLFiddle demo