Row to column transformation in MySQL

问题:

I have this result set in MySQL :

ID          Type Email          Degignation  
1000000000  202 [e-mail] Entrepreneur
1000000000  234 [e-mail] Engineering,Development
1000000000  239 [e-mail] 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       [e-mail]

回答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.

回答2:

Although Bill Karwin's answer is right query must know the defined set of columns but for a dynamic pivot query there is a hack way by using group_concat

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('MAX(CASE WHEN `Type` = ''',
         `Type`, 
         ''' THEN Degignation END) `Type_',
         `Type`,
         '`'
         )

 )
  INTO @sql
  FROM t;

SET @sql = CONCAT('SELECT ID, ', @sql, ', Email 
                     FROM t 
                    GROUP BY ID,Email');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See Demo

---

But as using group_concat it has default limit of 1024 characters to concatenate and the remaining result will be truncated so if there is lots of distinct types you have then this will tricky,Although you can increase the limit for group_concat length constraint as mentioned in manual but it also has a dependency on max_allowed_packet