Is it possible to merge data with same id and different value into another table?

Question

I am trying to separate all the data that has the same ID and different value and add them as columns in another table. Here is what I mean, this is the data structure of table "users_temp":

ID      user_pass user_nicename user_registered        meta_key         meta_value
1       user1       iamuser     2020-11-28 12:07:06   description   This is my description
1       user1       iamuser     2020-11-28 12:07:06   first_name    John
1       user1       iamuser     2020-11-28 12:07:06   last_name     Doe
1       user1       iamuser     2020-11-28 12:07:06   icon          icon_1
1       user1       iamuser     2020-11-28 12:07:06   theme         white
1       user1       iamuser     2020-11-28 12:07:06   gender        m

And this is how I want the data be structured in "users" table:

ID      user_pass user_nicename user_registered            description         first_name last_name  icon    theme  gender
1       user1       iamuser     2020-11-28 12:07:06   This is my description     John         Doe    icon_1  white  m

This is what I tried to do:

INSERT INTO users SELECT * FROM users_temp (CASE meta_key = 'description' THEN meta_value END),
(CASE meta_key = 'first_name' THEN meta_value END),
(CASE meta_key = 'last_name' THEN meta_value END),
(CASE meta_key = 'gender' THEN meta_value END),
(CASE meta_key = 'icon' THEN meta_value END),
(CASE meta_key = 'theme' THEN meta_value END)

As I can see it isn't correct. However, how can I achieve what I need? Thanks in advance!

you should check this answer in thread https://stackoverflow.com/a/9668036/7174186 firstly — buithienquyet, Dec 08 '22 at 16:36

score 1 · Accepted Answer · answered Dec 08 '22 at 16:52

You can use conditional aggregation.

insert into users (ID ,
                  user_pass ,
                  user_nicename ,
                  user_registered ,
                  description ,
                  first_name ,
                  last_name ,
                  icon ,
                  theme ,
                  gender )
select ID,
       user_pass,
       user_nicename,
       user_registered,
       max(case when meta_key ='description' then meta_value end) as description,
       max(case when meta_key ='first_name' then meta_value end) as first_name,
       max(case when meta_key ='last_name' then meta_value end) as last_name,
       max(case when meta_key ='icon' then meta_value end) as icon,
       max(case when meta_key ='theme' then meta_value end) as theme,
       max(case when meta_key ='gender' then meta_value end) as gender
from users_temp
group by ID,user_pass,user_nicename,user_registered;

https://dbfiddle.uk/A5SoDh6E

It shows me this error: `#1136 - Column count doesn't match value count at row 1` — Brad Cruise, Dec 08 '22 at 17:15
@BradCruise have you verified the fiddle in my answer? The error is because , i guess you are using insert into select * from and have have some missing/more columns — Ergest Basha, Dec 08 '22 at 17:26

Is it possible to merge data with same id and different value into another table?

1 Answers1