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

You can use self join:

SELECT t1.post_id AS ID, t1.meta_value AS Firstname, t2.meta_value AS LastnameFROM tab t1JOIN 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 EmailFROM tab t1JOIN 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')        )