CODEWITHSUNDEEP
phpmysqljoin
user2248391
user2248391

Reputation: 13

Combine Two Rows in MySQL to Show Data as One Row

I want to join WooCommerce created post meta fields to show first and last name of client together in one row.

The table structure of mysql is as follow:

meta_id    post_id    meta_key                meta_value
=======    =======    =======                 ===========
1234       1874       _billing_first_name     John
1235       1874       _billing_last_name      Cobe

Now I want it to return following results using mysql statement

post_id    _billing_first_name    _billing_last_name
=======    ====================   ==================
1874       John                   Cobe

Looking forward to your suggestions and help.

Thanks.

Upvotes: 1

Views: 1403

Answers (2)

Georgi Iliev
Georgi Iliev

Reputation: 65

Take a look at the PIVOT function. I think it will work perfectly for you.
Here is an example in SQL Fiddle

SELECT *
FROM
(
  SELECT post_id, meta_key, meta_value
  FROM table_name WHERE post_id = 1874
) as x
PIVOT
(
  MAX(meta_value)
  FOR meta_key IN ([_billing_first_name], [_billing_last_name])
) as p

Upvotes: 0

fthiella
fthiella

Reputation: 49059

If you have two fixed values for meta_key, you can use something like this:

SELECT
  post_id,
  MAX(CASE WHEN meta_key='_billing_first_name' THEN meta_value END) _billing_first_name,
  MAX(CASE WHEN meta_key='_billing_last_name' THEN meta_value END) _billing_last_name
FROM
  yourtable
GROUP BY
  post_id

Upvotes: 5

Related Questions