将表B中的多行链接到MySQL中的表A中的一行

I have two tables, for simplicity, table A and table B (Note, I don't have the authority to change the structure of these tables, so I'm asking this question to get around a bad database design).

Table A has two columns:

"id"          - is the unique identifier.
"customer_id" - is the customer's ID.

So table A holds a list of customer IDs.

Table B holds properties about customers. But it does it in a weird way (again, I didn't set this up, and I can't change it). Table B has [NUMBER] columns:

"id"          - is the unique identifier.
"customer_id" - is the customer's ID.
"key"         - is the name of the key/value pair
"value"       - is the value of the key/value pair

So table B holds key/value pairs that are linked to customers by their ID.

I could join these two tables to get something like this:

+----+-------------+------------+-------+
| id | customer_id | key        | value |
+----+-------------+------------+-------+
| 0  | 5           | first_name | Bob   |
| 1  | 5           | last_name  | Jones |
| 2  | 6           | first_name | Sally |
| 3  | 6           | last_name  | Sue   |
+----+-------------+------------+-------+

But as you can see, that can be difficult to manage because information about one customer is on two different rows. What would be ideal is something like this:

+----+-------------+------------+-----------+
| id | customer_id | first_name | last_name |
+----+-------------+------------+-----------+
| 0  | 5           | Bob        | Jones     |
| 1  | 6           | Sally      | Sue       |
+----+-------------+------------+-----------+

With all of the customer's data on one row.

Is there a way to do this in a SQL query so that I don't have to mess with the results in PHP? Or will I have to pick through the data in PHP?

One method is conditional aggregation:

select (@rn := @rn + 1) as id, customer_id,
       max(case when `key` = 'first_name' then value end) as first_name,
       max(case when `key` = 'last_name' then value end) as last_name
from b cross join
     (select @rn := 0) params
group by customer_id;

I'm not sure what table a would be used for, perhaps for filtering the customer ids.

Use Group_concat or Group by

Select *,Group_concat(value) as full_name
From b left join a on b.customer_id=a.customer_id
Group by customer_id

Given that you cannot alter the table structure, one approach is to use subselects on the customer_id and key:

SELECT
  tableA.id,
  tableA.customer_id,
  (
     SELECT 
        tableB.`value`
     FROM tableB 
     WHERE tableB.customer_id = tableA.customer_id 
       AND tableB.`key` = 'first_name'
  ) AS first_name,
  (
     SELECT 
        tableB.`value`
     FROM tableB 
     WHERE tableB.customer_id = tableA.customer_id 
       AND tableB.`key` = 'last_name'
   ) AS last_name
FROM tableA

Note: Performance-wise this query might suck. But, if you are out of options, maybe the slow query will drive the people who make decisions to allow the structure to be changed.

This will do what you want:

SELECT A.customer_id, B.value, B_1.value
FROM (A INNER JOIN B ON A.customer_id = B.customer_id) 
INNER JOIN B AS B_1 ON B.customer_id = B_1.customer_id
WHERE (((B.key)="first_name") AND ((B_1.key)="last_name"));