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.

Bill Karwin · Accepted Answer · 2013-11-19T18:56:01.780

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.

score 2 · Answer 2 · answered Nov 19 '13 at 00:37

You can do it using this:

SELECT GROUP_CONCAT(CASE WHEN type = 0 THEN type END ORDER BY Type) AS types_0,
       GROUP_CONCAT(CASE WHEN type = 0 THEN name END ORDER BY Type) AS names_0,
       GROUP_CONCAT(CASE WHEN type = 0 THEN `key` END ORDER BY Type) AS keys_0,
       GROUP_CONCAT(CASE WHEN type = 1 THEN type END ORDER BY Type) AS types_1,
       GROUP_CONCAT(CASE WHEN type = 1 THEN name END ORDER BY Type) AS names_1,
       GROUP_CONCAT(CASE WHEN type = 1 THEN `key` END ORDER BY Type) AS keys_1
FROM   Test;

Example on SQL Fiddle

Or if you don't know the number of types you can generate the SQL dynamically and use a prepared statement:

SET @SQL = '';

SELECT CONCAT('SELECT ',
                GROUP_CONCAT(DISTINCT
                    CONCAT('GROUP_CONCAT(CASE WHEN type = ', type, ' THEN type END ORDER BY Type) AS types_', type, ','
                            'GROUP_CONCAT(CASE WHEN type = ', type, ' THEN name END ORDER BY Type) AS names_', type, ','
                            'GROUP_CONCAT(CASE WHEN type = ', type, ' THEN `key` END ORDER BY Type) AS keys_', type
                    )), ' FROM Test;')
INTO    @SQL
FROM    Test;

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Example on SQL-Fiddle

HOWEVER, just because you can do something, doesn't mean you should, and I agree with Bill Karwin, the most sensible way to display this data would be to group by type. In addition your query is not determinisitic, you should order by something more than just type, to ensure the order of the names and keys are consistent.

+1 yes, I agree one should sort by something other than `type` which is by definition at a tie within each group by type. I'll edit my answer to show that. — Bill Karwin, Nov 19 '13 at 18:54

MySQL query with group contact

2 Answers2