Transposing Dynamic Columns to Rows

by

MySQL does not have an UNPIVOT function, but you can convert your columns into rows using a UNION ALL.

The basic syntax is:

select id, word, qty
from
(
  select id, 'abc' word, abc qty
  from yt
  where abc > 0
  union all
  select id, 'brt', brt
  from yt
  where brt > 0
) d
order by id;

In your case, you state that you need a solution for dynamic columns. If that is the case, then you will need to use a prepared statement to generate dynamic SQL:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
where c.table_name="yt"
  and c.column_name not in ('id')
order by c.ordinal_position;

SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');


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

See SQL Fiddle with Demo

More Related Contents:

  1. MySQL – How to unpivot columns to rows?
  2. Mysql Convert Column to row (Pivot table )
  3. MySQL: How do I use delimiters in triggers?
  4. MySQL remove all whitespaces from the entire column
  5. True/False vs 0/1 in MySQL
  6. Update MySQL with if condition
  7. How does MySQL handle concurrent inserts?
  8. CAST to DECIMAL in MySQL
  9. Retrieving the coordinates of the MySQL point type
  10. “Unknown column in ‘field list'”, but column does exist
  11. MySQL connection over SSH tunnel
  12. MySQL licensing and GPL [closed]
  13. How do I combine the results of two queries with ordering?
  14. How to do unique constraint works with NULL value in MySQL
  15. QMYSQL driver available but not loaded
  16. Database Historization
  17. How we can find domain name using MySQL and regular expression
  18. Transpose mysql query rows into columns
  19. UTF8 problem with MySQL 5
  20. make an ID in a mysql table auto_increment (after the fact)
  21. Bind address and MySQL server [closed]
  22. mySQL returns all rows when field=0
  23. When is using MySQL BLOB recommended?
  24. MYSQL Select One Random record from each Category
  25. mysql order by, null first, and DESC after
  26. How do I make a MySQL database run completely in memory?
  27. Mysql Average on time column?
  28. How to recover just deleted rows in mysql?
  29. In MySQL SERVER 8.0 the PASSWORD function not working
  30. Using OR in LIKE Query in MySQL to compare multiple fields

Leave a Comment