Combine multiple rows in multiple colums with mysql

Question

I am tring to export some data from a wordpress plugin that save 3 custom filed (name, surname and email) in multiple rows. Every filed have a "meta_key" to identify it (so it is "simple" to understand the field name) but in my DB I need a single row with multiple columns.

Example:

WP database (named "table_postmeta")

  post_id | meta_key | meta_value
      1   |    7a4   |   Name1
      1   |    f53   |   Surname1
      1   |    8ff   |   test1@test.com
      2   |    7a4   |   Name2
      2   |    f53   |   Surname2
      2   |    8ff   |   test2@test.com
      3   |    7a4   |   Name3
      3   |    f53   |   Surname3
      3   |    8ff   |   test3@test.com

My table (named "users", I will create a copy of _postmeta table in my db so I always have a clear copy):

post_id | Name   | Surname  | Mail
  1     | Name1  | Surname1 | test1@test.com
  2     | Name2  | Surname2 | test2@test.com
  3     | Name3  | Surname3 | test3@test.com

Is there a way to do this with mysql or it is better to do a php script?

Thanks!

This transformation is called pivoting and can be fone both in mysql or in the application (php). We cannot tell you which one would be better for your particular use case. I gave you a duplicate question that shows you how to do this transformation in mysql. — Shadow, Jul 21 '21 at 06:32

score 0 · Accepted Answer · answered Jul 21 '21 at 06:18

It is up to your level of experience and maintenance style as to what is best, in fact SO strongly discourages these type of questions that will attract opinionated responses.

If you have other scripted logic, then it might be simpler long term if this parsing process is contained within that script.

If however most of your coded logic is within the database, then you could do this in pure SQL as part of the CRUD script that you are using now, or you could formally provide a stored procedure to manage this for you.

It will depend largely on where the meta_key is defined and which code environment has access to it. If the database context cannot resolve this then you will need to do it in a script.

For this type of issue it is better to try one way first, then ask for help when you need it. For something as simple as this what is BEST is largely irrelevant, as long as it WORKS for you and you know how to maintain it.

Combine multiple rows in multiple colums with mysql

1 Answers1