Row to column transformation in MySQL

by

This is called a pivot table. It’s kind of awkward to produce:

SELECT ID, 
 MAX(CASE Type WHEN 202 THEN Degignation END) AS `202`
 MAX(CASE Type WHEN 234 THEN Degignation END) AS `234`
 MAX(CASE Type WHEN 239 THEN Degignation END) AS `239`
 Email
FROM mytable
GROUP BY ID, Email

Note that you must know all the distinct Type values before you write the query. SQL doesn’t allow a result set to add more columns dynamically as it discovers data values in the table. Columns must be fixed at query prepare time.

More Related Contents:

  1. MySQL – Rows to Columns
  2. mysql select dynamic row values as column names, another column as value
  3. Mysql query to dynamically convert rows to columns on the basis of two columns
  4. MySQL dynamic pivot table
  5. MySQL dynamic cross tab
  6. left join query keep a column empty
  7. Rank function in MySQL
  8. MySQL 8.0 – Client does not support authentication protocol requested by server; consider upgrading MySQL client
  9. Can’t connect to MySQL server error 111 [closed]
  10. Include headers when using SELECT INTO OUTFILE?
  11. ERROR 1130 (HY000): Host ” is not allowed to connect to this MySQL server [duplicate]
  12. MySQL timezone change?
  13. ON DUPLICATE KEY + AUTO INCREMENT issue mysql
  14. Cronjob or MySQL event?
  15. How to insert BLOB and CLOB files in MySQL?
  16. 60 million entries, select entries from a certain month. How to optimize database?
  17. How to start spring-boot app without depending on Database?
  18. Is it possible to GROUP BY multiple columns using MySQL?
  19. MySQL compare DATE string with string from DATETIME field
  20. MySQL monthly Sale of last 12 months including months with no Sale
  21. Can MySQL FIND_IN_SET or equivalent be made to use indices?
  22. How to fuzzy match email or telephone by Elasticsearch?
  23. How can I use transactions in my MySQL stored procedure?
  24. MySQL – select data from database between two dates
  25. Calculating a Moving Average MySQL?
  26. DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled
  27. Can’t access mysql from command line on a Mac
  28. MySQL OPTIMIZE all tables?
  29. Strip last two characters of a column in MySQL
  30. mysql double-quoted table names

Leave a Comment