CODEWITHSUNDEEP
phpmysqlsqljsonwordpress
Virik
Virik

Reputation: 407

MySQL - Display rows as columns (as simple as possible)

I've been having some trouble with the following for a couple of weeks. I've read all the tutorials and snippets I've found, but can't make this work.

I have the following table (wordpress database):

post_id  meta_key              meta_value
802      _billing_first_name   John
802      _billing_last_name    Johnson
802      _billing_first_name2  Jack
802      _billing_last_name2   Jackson
802      _billing_first_name3  Jason
802      _billing_last_name3   Jasonson
803      _billing_first_name   Jamie
803      _billing_last_name    Jameson
803      _billing_first_name2  Oliver
803      _billing_last_name2   Olverson

I need a list of all persons from all the orders. The list should look like this.

ID    Firstname    Lastname
802   John         Johnson
802   Jack         Jackson
802   Jason        Jasonson
803   Jamie        Jameson
803   Oliver       Oliverson

Each order may have unlimited names. One order may be for 20 persons, while the next could be a single person.

Upvotes: 2

Views: 71

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You can use self join:

SELECT t1.post_id AS ID, t1.meta_value AS Firstname, t2.meta_value AS Lastname
FROM tab t1
JOIN tab t2
  ON t1.post_id = t2.post_id
 AND t1.meta_key LIKE '_billing_first_name%'
 AND t2.meta_key LIKE '_billing_last_name%'
 AND RIGHT(t1.meta_key, 3) = RIGHT(t2.meta_key,3);  -- up to 999 users

LiveDemo

Output:

╔═════╦═══════════╦══════════╗
║ ID  ║ FirstName ║ LastName ║
╠═════╬═══════════╬══════════╣
║ 802 ║ John      ║ Johnson  ║
║ 802 ║ Jack      ║ Jackson  ║
║ 802 ║ Jason     ║ Jasonson ║
║ 803 ║ Jamie     ║ Jameson  ║
║ 803 ║ Oliver    ║ Olverson ║
╚═════╩═══════════╩══════════╝

The point is MySQL is relational database and does not work well with EAV design. This solution may be slow in very big tables because join condition is non-SARGable.

EDIT:

I guess you want to join:

_billing_first_name2 with _billing_email002

You can use to handle 000 with normal numbers, but performance will be poor:

SELECT t1.post_id AS ID, t1.meta_value AS Firstname, t2.meta_value AS Email
FROM tab t1
JOIN tab t2
  ON t1.post_id = t2.post_id
 AND t1.meta_key LIKE '_billing_first_name%'
 AND t2.meta_key LIKE '_billing_last_email%'     --email
 AND CONCAT(
      IF(SUBSTRING(t1.meta_key,-3,1) REGEXP '[0-9]',SUBSTRING(t1.meta_key,-3,1), '0'),
      IF(SUBSTRING(t1.meta_key,-2,1) REGEXP '[0-9]',SUBSTRING(t1.meta_key,-2,1), '0'),
      IF(SUBSTRING(t1.meta_key,-1,1) REGEXP '[0-9]',SUBSTRING(t1.meta_key,-1,1), '0')
        ) = 
      CONCAT(
      IF(SUBSTRING(t2.meta_key,-3,1) REGEXP '[0-9]',SUBSTRING(t2.meta_key,-3,1), '0'),
      IF(SUBSTRING(t2.meta_key,-2,1) REGEXP '[0-9]',SUBSTRING(t2.meta_key,-2,1), '0'),
      IF(SUBSTRING(t2.meta_key,-1,1) REGEXP '[0-9]',SUBSTRING(t2.meta_key,-1,1), '0')
        )

Upvotes: 3

Related Questions