mysql select dynamic row values as column names, another column as value

by

Unlike some other RDBMS MySQL doesn’t have native support for pivoting operations of this sort by design (the developers feel it’s more suited to the presentation, rather than database, layer of your application).

If you absolutely must perfom such manipulations within MySQL, building a prepared statement is the way to go—although rather than messing around with CASE, I’d probably just use MySQL’s GROUP_CONCAT() function:

SELECT CONCAT(
  'SELECT `table`.id', GROUP_CONCAT('
     ,    `t_', REPLACE(name, '`', '``'), '`.value
         AS `', REPLACE(name, '`', '``'), '`'
     SEPARATOR ''),
 ' FROM `table` ', GROUP_CONCAT('
     LEFT JOIN `table`   AS `t_', REPLACE(name, '`', '``'), '`
            ON `table`.id = `t_', REPLACE(name, '`', '``'), '`.id
           AND `t_', REPLACE(name, '`', '``'), '`.name=", QUOTE(name)
     SEPARATOR "'),
 ' GROUP BY `table`.id'
) INTO @qry FROM (SELECT DISTINCT name FROM `table`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;

See it on sqlfiddle.

Note that the result of GROUP_CONCAT() is limited by the group_concat_max_len variable (default of 1024 bytes: unlikely to be relevant here unless you have some extremely long name values).

More Related Contents:

  1. MySQL – Rows to Columns
  2. Mysql query to dynamically convert rows to columns on the basis of two columns
  3. how to delete data from table A besides the customer service and director data in table B?
  4. Get records with max value for each group of grouped SQL results
  5. MYSQL OR vs IN performance
  6. SELECTING with multiple WHERE conditions on same column
  7. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  8. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  9. Using union and order by clause in mysql
  10. Using an Alias in SQL Calculations
  11. Can a table field contain a hyphen?
  12. Can MySQL replace multiple characters?
  13. OUTPUT Clause in MySQL
  14. Search all columns of a table using a single where condition with single keyword in mysql
  15. What does SQL clause “GROUP BY 1” mean?
  16. Using ORDER BY and GROUP BY together
  17. Sorting by date & time in descending order?
  18. Case statement in MySQL
  19. MySQL Pivot Table Column Data as Rows
  20. updating columns with a sequence number mysql
  21. MySQL, Concatenate two columns
  22. Select a column with a keyword name
  23. MySQL: Inner join vs Where [duplicate]
  24. Syntax error when using except in a query
  25. Selecting from two tables with inner join and limit [duplicate]
  26. Use Composite Primary Key as Foreign Key
  27. Why the order of evaluation for expressions involving user variables is undefined?
  28. How to select data where a field has a min value in MySQL?
  29. How to SELECT by MAX(date)?
  30. get a recursive parent list

Leave a Comment