Mysql convert table from long format to wide format

Question

I have a MySQL table like this:

And I want to convert it from long format to wide format like this

Sorry. I'm new and don't know how to post a table

you should use the pivot table to get it.. – jmail Mar 28 '14 at 13:27 — jmail, Mar 28 '14 at 13:27

score 8 · Answer 1 · answered Mar 28 '14 at 13:39

You need something like this:

select id,
       max(case when info='firstname' then value else null end), 
       max(case when info='lastname' then value else null end), 
       ...
   from table
   group by id;

score 3 · Accepted Answer · answered Mar 28 '14 at 13:50

3

Try this:

insert into goodTable 
select
bt.id,
(select bt1.value from badTable bt1 where bt1.info = 'firstname' and bt1.id = bt.id),
(select bt1.value from badTable bt1 where bt1.info = 'lastname' and bt1.id = bt.id),
(select bt1.value from badTable bt1 where bt1.info = 'phone' and bt1.id = bt.id)
from
 badTable bt
group by id ;

Working fiddle here: http://sqlfiddle.com/#!2/45f29e/2

answered Mar 28 '14 at 13:50

Hackerman

12,139
2
34
45

Great answer, i use your commands and it works fine, thanks you very much – Luvias Mar 28 '14 at 15:59
1

How does this optimize compared to a pure `group by` solution with no subqueries? – Mar 28 '14 at 16:00
How about if city and address gets added to info? Then those also should be converted to new columns like firstname and lastname. Is there a way to dynamically handle this instead of hardcoding in inner query? – codninja0908 Jun 22 '20 at 17:42

score -1 · Answer 3 · answered Mar 28 '14 at 13:50

try this:

SELECT  P.`info`,
        (CASE 
            WHEN P.`action`='fname' AND P.`id` = '1'OR'2' 
            THEN P.`id` 
            ELSE NULL 
        END)AS 'firstname',

        (CASE 
            WHEN P.`action`='lname' AND P.`id` = '2' OR'2'
            THEN P.`id` 
            ELSE NULL 
        END) AS 'lastname',

        (CASE 
            WHEN P.`action`='phone' AND P.`id` = '1'OR'2' 
            THEN P.`id` 
            ELSE NULL 
        END) AS 'phone'
FROM    tablename P
GROUP BY P.`info`;

The inclusion of the conditions on `ID` in your case statements makes no sense. — , Mar 28 '14 at 16:02

Mysql convert table from long format to wide format

3 Answers3