cyberfly
9/14/2017 - 2:09 PM

MYSQL Pivot row to dynamic column with filter (wp post meta style) https://stackoverflow.com/a/21118989/417899 http://buysql.com/mysql/14-h

MYSQL Pivot row to dynamic column with filter (wp post meta style)

https://stackoverflow.com/a/21118989/417899 http://buysql.com/mysql/14-how-to-automate-pivot-tables.html http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/

new_gist_file.sql
content_copyfile_download
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when t2.`meta_key` = ''',
      `meta_key`,
      ''' then t2.meta_value end) AS `',
      `meta_key`, '`'
    )
  )
INTO @sql
from strategic_plan_metas;

SET @sql 
    = CONCAT('SELECT t1.id, t1.name, ', @sql, ' 
              from strategic_plan_structures t1
              left join strategic_plan_metas t2
                on t1.id = t2.strategic_plan_id
              group by t1.id, t1.name
              having brand="iphone"
              ;');

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