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!
CodePudding user response：
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;