Combine multiple rows into one MySQL

Question

I need some help. I googled for solution, but I didn't found one.

I have three tables:

langs
(int) id | (varchar) language
    1    |       English
    2    |        Latin
    3    |      Esperanto
   ...   |         ...

keys
(int) id | (varchar) keys
    1    |       dog
    2    |       cat
   ...   |       ...

value
(int) id | (int) key_id | (int) lang_id | (varchar) value
    1    |       1      |        1      |        Dog
    2    |       1      |        2      |       Canis
    3    |       2      |        1      |        Cat
    4    |       2      |        2      |       Felis
    5    |       2      |        3      |        Kato
   ...   |      ...     |       ...     |        ...

and I want to get the result:

key_id |  keys  |  English  |  Latin  |  Esperanto  
   1   |  dog   |    Dog    |  Canis  |    NULL
   2   |  cat   |    Cat    |  Felis  |    Kato
  ...  |  ...   |    ...    |   ...   |    ...

merging rows with equal key_id. I know I can try to make it with multiple JOINs, but it's slow and I must know the exact number of different langs.

Thanks in advance. :)

possible duplicate of [Combine Multiple child rows into one row MYSQL](http://stackoverflow.com/questions/1067428/combine-multiple-child-rows-into-one-row-mysql) — Ryan, Sep 04 '12 at 20:36

eggyal · Accepted Answer · 2014-03-28T20:45:49.393

2

SELECT CONCAT(
  ' SELECT `value`.`key_id`,'
,        '`keys`.`keys`,'
,         GROUP_CONCAT(
           'GROUP_CONCAT(IF(`value`.`lang_id`=',id,',`value`.`value`,NULL))'
          ,   ' AS `', REPLACE(language, '`', '``'), '`'
          )
, ' FROM `keys` JOIN `value` ON `value`.`key_id` = `keys`.`id`'
, ' GROUP BY `value`.`key_id`'
)
INTO @sql
FROM `langs`;

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

edited Mar 28 '14 at 20:45

answered Sep 04 '12 at 20:48

eggyal

122,705
18
212
237

That's exactly what I need but there is a problem. When I reproduce it on my localhost DB via phpMyAdmin, I get error `#1243 - Unknown prepared statement handler (stmt) given to EXECUTE`. The code is working when I select the `@sql` variable and run it. I tried with other prepared statements, they are working too. I googled for similar problems, but couldn't find solution. – suricactus Sep 04 '12 at 23:47
1

@erik1001: Are you using the same statement handler in your `PREPARE` statement? – eggyal Sep 05 '12 at 06:14
Of course it has the same name, but anyway I have to do it with separate queries, one to build the stmt and one do execute it, because CI does not allow multiple queries, so everything is OK. Thank you very much, you have my upvotes. :) – suricactus Sep 13 '12 at 01:58

score 0 · Answer 2 · answered Sep 04 '12 at 20:39

You are essentially looking at a pivot table. It Is doable, though the code is a bit cumbersome.

one source: http://www.artfulsoftware.com/infotree/queries.php#78

In the end though most any mysql solution will require either some wizardry with stored procedures or... hardcoding what languages you return.

Pivot tables are one area that mysql doesn't shine in.

Combine multiple rows into one MySQL

2 Answers2

Linked