Combine multiple rows into a one using sql

Question

In a sql database, I have this:

And I want to create another table that shows the data like this:

Which query should I use?

Answer 1

Possible duplicate of: MySQL pivot table query with dynamic columns. However, what you want is a "pivot" query. You can do this dynamically so you can continue to add new field names and don't have to worry about it pulling those in. Here is the sample code in that question, which would be very similar to what you're trying to do:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.fieldname = ''',
      fieldname,
      ''', pa.fieldvalue, NULL)) AS ',
      fieldname
    )
  ) INTO @sql
FROM product_additional;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id');

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

And here's a great tutorial on doing this: http://buysql.com/mysql/14-how-to-automate-pivot-tables.html