Pivot MySQL - How to move data from one table to a new one

Question

Let's say I have an old_table with 74 columns resumed here:

ID  NICKNAME    FIRST_NAME  LAST_NAME
7   Nick1       Name1       Lastname1
8   Nick2       Name2       Lastname2

And I need to have this data moved and pivoted to an new_table in MySQL for import the users data to Wordpress.

UMETA_ID    USER_ID     META_KEY    META_VALUE
1           7           NICKNAME    Nick1
2           7           FIRST_NAME  Name1
3           7           LAST_NAME   Lastname1
4           8           NICKNAME    Nick2
5           8           FIRST_NAME  Name2
6           8           LAST_NAME   Lastname2

How can I achieve this in MySQL? Thanks in advance and all the best for whom that can help a fish out of his water.

Answer 1

You can start from:

http://sqlfiddle.com/#!9/57543/2

INSERT INTO new (user_id, meta_key, meta_value) 
SELECT old.id, 'NICKNAME', old.nickname FROM old
UNION
SELECT old.id, 'FIRST_NAME', old.first_name FROM old
UNION
SELECT old.id, 'LAST_NAME', old.last_name FROM old