Transpose rows of sheet 1 to sheet2 based on a column value

Question

I have two sheets below. I have also added sample data and desired output in the sheet here.

Posts Sheet:

 id |   title   |  image   
 1  |  title 1  |  1.jpg   
 2  |  title 2  |  2.jpg   
 3  |  title 3  |  3.jpg   

Metadata Sheet:

  meta_id |  meta_key      |  value   |  post_id
     1    |  meta_title    |  1       |     1
     2    |  meta_keyword  |  1       |     1
     3    |  meta_likes    |  1       |     1
     4    |  meta_title    |  2       |     2
     5    |  meta_keyword  |  2       |     2
     7    |  meta_title    |  3       |     3
     8    |  meta_keyword  |  3       |     3
     9    |  meta_likes    |  3       |     3

As you can see, there is no guarantee every post will have all the metadata.

Explanation on each sheet:

Posts Sheet -

Posts sheet consist of a list of posts, its title and image. id column in it is unique and will never repeat. So basically one post will have one row.

Metadata Sheet -

Metadata sheet consists of various information related to an individual post. It contains key-value data with a mapping to the main post sheet via the post_id column.

How both need to be joined-

Each metadata in the metadata sheet should be added to relevant rows in the posts sheet based on the post_id column in metadata. So after merging a row in the posts sheet will have columns named meta_title, meta_keyword & meta_likes.

And example of the column headers after merge will look as below:

meta_id | meta_key  |  value  |  meta_title | meta_keyword | meta_likes

I tried this, however, it only works if meta_table has just one key. In my case, the metatable can consist of multiple keys and values.

Sample data & desired output added in this sheet.

Answer 1

on this shared sheet from the comments in the original post, I made a new tab called MK.Help where I left two options. The first consists of two formulas, the second, just one.

This formula in cell A1, just brings over the posts:

=QUERY(Posts!A:C,"where B<>''")

This formula populates the meta data for each post.

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A&D1:F1,{Metadata!D:D&Metadata!B:B,Metadata!C:C},2,0)))

There is also another potentially simpler option which just QUERY()s the meta data in a fixed way. That is, you cannot control the order of the columns, they happen alphabetically.

=QUERY(Metadata!A:D,"select D,MAX(C) where D is not null group by D pivot B")