By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table users_temp (
ID int,
user_pass varchar(30),
user_nicename varchar(30),
user_registered datetime,
meta_key varchar(30),
meta_value varchar(30) );
insert into users_temp values
(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');
create table users (
ID int,
user_pass varchar(30),
user_nicename varchar(30),
user_registered datetime,
description varchar(30),
first_name varchar(30),
last_name varchar(30),
icon varchar(30),
theme varchar(30),
gender varchar(30) );
Records: 6 Duplicates: 0 Warnings: 0
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;
| 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 |
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;
Records: 1 Duplicates: 0 Warnings: 0
select * from users;
| 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 |