MySQL 8.0

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