Row to column transformation in MySQL
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]
Solution 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.
Solution 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 forgroup_concat
length constraint as mentioned in manual but it also has a dependency onmax_allowed_packet