Create new table with data from Wordpress wp_postmeta table

Question

The goal is to migrate the data related to a product definition stored in a Wordpress table (in this case, credit cards).

Here is an example of the data I want to migrate:

SELECT meta_key, meta_value FROM wp_postmeta WHERE post_id = 366;

+-----------------+------------------------------------+
| meta_key        | meta_value                         |
+-----------------+------------------------------------+
| Name            | Santos Futebol Clube International |
| Issuer          | Bradesco Banco                     |
| Issuer_Subtype  | Bradesco                           |
+-----------------+------------------------------------+

There are many more fields, but I show three of them just as an example. There is also many product items (each post represents an item).

I want to migrate these data into a table with the following structure:

DESCRIBE card;

+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name                  | varchar(255) | YES  |     | NULL    |                | 
| issuer                | varchar(255) | YES  |     | NULL    |                |
| issuer_subtype        | varchar(255) | YES  |     | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+

Since the data in the Wordpress table representing each item is located in different rows, I'm having problems trying to grasp my head around how to make a query that can achive this.

Any suggestions?

Answer 1

Group by each post_id. Then you can select the meta_values for the key you like with a case. You have to use aggregate functions. I chose max() but that does not matter.

select post_id, 
       max(case when meta_key = 'Name' then meta_value end) as Name,
       max(case when meta_key = 'Issuer' then meta_value end) as Issuer,
       max(case when meta_key = 'Issuer_Subtype' then meta_value end) as Subtype
FROM wp_postmeta
group by post_id