pivot table - Row to column transformation in MySQL

Question

I have this result set in MySQL :

ID          Type Email          Degignation  
1000000000  202 [email protected] Entrepreneur
1000000000  234 [email protected] Engineering,Development
1000000000  239 [email protected] CTO

I have many such tuples not only three . I want Type to be column and last column to become row value for them . Like below

ID                202         234                     239        Email 
1000000000   Entrepreneur  Engineering,Development    CTO       [email protected]

question from:https://stackoverflow.com/questions/65947863/add-custom-column-from-existing-row

Answer 1

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.