How to group mysql inner join results from rows into columns

Question

Maybe my question isn't so clear, please let me explain: What I need is to get a list of all users along with a corresponding client number/wholesaler combination, every client has 1 up to 4 different client number/wholesaler combo

In my db I have 2 tables

USERS
id | name
---------
1  | a
2  | b

CLIENT_NUMBERS
id | user_id | number | wholesaler
----------------------------------
1  | 1       | ac1    | aw1
2  | 1       | ac2    | aw2
3  | 2       | bc1    | bw1

Using a simple INNER JOIN I got client rows duplicated, one for each corresponding client number/wholesaler

I've managed to fix the results using GROUP_CONCAT in this query:

SELECT a.id AS user_id, a.name AS Name 
GROUP_CONCAT(b.client_no, ', ', b.wholesaler SEPARATOR '; ') AS client_no_wholesaler 
FROM users AS a 
INNER JOIN client_numbers AS b ON a.id = b.user_id 
GROUP BY ID

user_id | name | client_no_wholesaler
--------------------------------------------
1       | a    |  ac1, aw1; ac2, aw2 
2       | b    |  bc1, bw1

So far so good, but I need to "explode" the client number/wholesaler combination into different columns so my results can look like this:

user_id | name | client_no_wholesaler1 | client_no_wholesaler2 | ...up to 4
----------------------------------------------------------------------------
1       | a    |  ac1, aw1             | ac2, aw2              |
2       | b    |  bc1, bw1             |                       |

Doing this after getting the query results with a simple PHP explode is not an option because I'm using a class to generate a XLS file and its based on my query result columns, any ideas will be appreciated.

Answer 1

What you want is generally called a "pivot".

Here's how you code it in sql using an extra join for each extra column:

SELECT
    a.id AS user_id,
    a.name AS Name,
    CONCAT(b1.client_no, ', ', b1.wholesaler) AS client_no_wholesaler1,
    CONCAT(b2.client_no, ', ', b2.wholesaler) AS client_no_wholesaler2,
    CONCAT(b3.client_no, ', ', b3.wholesaler) AS client_no_wholesaler3,
    CONCAT(b4.client_no, ', ', b4.wholesaler) AS client_no_wholesaler4
FROM users AS a
JOIN client_numbers AS b1 ON b1.user_id = a.id
LEFT JOIN client_numbers AS b2 ON b2.user_id = a.id and b2.id > b1.id
LEFT JOIN client_numbers AS b3 ON b3.user_id = a.id and b3.id > b2.id
LEFT JOIN client_numbers AS b4 ON b4.user_id = a.id and b4.id > b3.id
GROUP BY 1, 2

Note that the extra joins avoid duplicate joins by the addition of an ever-increasing id condition in the ON clause for the extra joins. If id isn't a suitable ordering column, chose something else to separate the joins.