MySQL query with group contact

Question

Let's say I have a table called "test" with the following design:

SELECT type, name, `key` FROM test

type | name    | key
------------------------
  0  | maria   | 123
  1  | gabriel | 455
  0  | rihanna | 69
  1  | chris   | 7
  1  | martin  | 112

 

 

The next query allows me to get all data in one line:

SELECT GROUP_CONCAT(type ORDER BY type) types, GROUP_CONCAT(name ORDER BY type) names, GROUP_CONCAT(`key` ORDER BY type) `keys` FROM test

  types   |               names                |      keys
------------------------------------------------------------------
0,0,1,1,1 | rihanna,maria,martin,chris,gabriel | 69,123,112,7,455

 

But that's not exactly what I need. It'd be perfect if I was able to create a query that returns the following result:

types_0 |     names_0    |  keys_0  | types_1 |         names_1         |    keys_1
------------------------------------------------------------------------------------
  0, 0  | maria, rihanna |  123, 69 |   1, 1  | gabriel, chris, martin  | 455, 7, 112

 

Is there any way to create such query? or wouldn't it even make sense at all?

Thanks in advance.

Answer 1

It is kind of possible but I wouldn't do it. It would look something like this:

SELECT * FROM 
(
  SELECT 
    GROUP_CONCAT(type ORDER BY type) types, 
    GROUP_CONCAT(name ORDER BY type) names, 
    GROUP_CONCAT(`key` ORDER BY type) `keys` 
  FROM test
  WHERE type = 0
) AS _type0,
(
  SELECT 
    GROUP_CONCAT(type ORDER BY type) types, 
    GROUP_CONCAT(name ORDER BY type) names, 
    GROUP_CONCAT(`key` ORDER BY type) `keys` 
  FROM test
  WHERE type = 1
) AS _type1;

There is no way to generate more columns dynamically if it finds more types. This is typical of pivot table queries -- you must know the distinct values before you write the query.

I would instead do this:

SELECT 
  type,
  GROUP_CONCAT(name ORDER BY name) names, 
  GROUP_CONCAT(`key` ORDER BY name) `keys` 
FROM test
GROUP BY type;

And the output should look like:

 type |         names        |  keys
------------------------------------------------------------------
 0    | maria,rihanna        | 123,69
 1    | chris,gabriel,martin | 7,455,112

edit: I made this query order by name within each group, per suggestion from @GarethD's answer.