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.
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.