MySQL – How to unpivot columns to rows?

by

You are trying to unpivot the data. MySQL does not have an unpivot function, so you will have to use a UNION ALL query to convert the columns into rows:

select id, 'a' col, a value
from yourtable
union all
select id, 'b' col, b value
from yourtable
union all
select id, 'c' col, c value
from yourtable

See SQL Fiddle with Demo.

This can also be done using a CROSS JOIN:

select t.id,
  c.col,
  case c.col
    when 'a' then a
    when 'b' then b
    when 'c' then c
  end as data
from yourtable t
cross join
(
  select 'a' as col
  union all select 'b'
  union all select 'c'
) c

See SQL Fiddle with Demo

More Related Contents:

  1. SQL Performance UNION vs OR
  2. Combining UNION and LIMIT operations in MySQL query
  3. Using union and order by clause in mysql
  4. UNION query with codeigniter’s active record pattern
  5. Mysql Convert Column to row (Pivot table )
  6. i have an error which i dont understand. can you help me? [closed]
  7. In SQL Server how to get the count of child if both parent and child are available [closed]
  8. Delete the entire content of all table in a Database [closed]
  9. MySQL select 10 random rows from 600K rows fast
  10. Get a list of dates between two dates
  11. Calculate a running total in MySQL
  12. MySQL SELECT only not null values
  13. Calculate Age in MySQL (InnoDb)
  14. Difference between two dates in MySQL
  15. MySQL search and replace some text in a field
  16. Datetime equal or greater than today in MySQL
  17. How to get the next auto-increment id in mysql
  18. MySQL dynamic-pivot
  19. MySQL Workbench: How to keep the connection alive
  20. Can I reuse a calculated field in a SELECT query?
  21. Deleting duplicate rows from a table
  22. Using Count to find the number of occurrences
  23. MySQL INSERT INTO … VALUES and SELECT
  24. MySQL LIKE query with underscore
  25. MYSQL select DISTINCT values in two columns
  26. Compare only day and month with date field in mysql
  27. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  28. MySQL OPTIMIZE all tables?
  29. Strip last two characters of a column in MySQL
  30. Deleting a row based on the max value

Leave a Comment