Mysql – group_concat in separate columns

group byMySQL

I have a players table

id  name
--  ----
1   jack
2   john
3   mike
4   pikachu

And I have a matches table where these players can be matched together.

id pair_id player_id
-- ------- ---------
 1       1         1
 2       1         2
 3       2         3
 4       2         4

So I tried something like select group_concat(name) group by pair_id
The end results is something like:

names
---------
Jack,John

But what I like to is to get players in their own columns like:

p1   p2
---- ----
Jack John

How can I do that?

Best Answer

No group_concat needed, just joins:

select player1.name as p1, player2.name as p2
from matches m1
join players player1 on m1.player_id = player1.id
join matches m2 on m1.pair_id = m2.pair_id and m1.id != m2.id
join players player2 on m2.player_id = player2.id;

Related Solutions

Postgresql – GROUP BY and ORDER BY problem

First off, your PRIMARY KEY spanning two varchar(2000) columns seems extremely expensive. If you use your PK for anything else I suggest a surrogate PK (use a serial column) and add a UNIQUE constraint to enforce uniqueness on (cid, name, synonym).

If one of your varchar columns actually uses the maximum length you would exceed the maximum size for an index entry. See:

Character varying index overhead & length limit

I guess what you want is this, because it would make sense:

SELECT DISTINCT ON (c.name)
       c.name, min(s.synonym) AS min_synonym, s.cid
     , ts_rank(s.tsv_syns, c.lexemes, 16) AS rnk
     , count(*) AS ct
FROM   synonyms_all_gin_tsvcolumn s
JOIN   cmap5                      c ON c.lexemes @@ s.tsv_syns
GROUP  BY c.name, rnk, s.cid
ORDER  BY c.name, rnk DESC, ct DESC;

I use explicit [INNER] JOIN with attached join condition replacing your CROSS JOIN plus WHERE clause. It's generally considered superior (easier to read and debug). I also use rnk as column name to avoid the basic function name rank as identifier.
Group the results per c.name that have the same rnk and s.cid, take min(s.synonym) (for lack of definition in the question), and count(*) the peers per group.
Reduce to one row per c.name with DISTINCT ON (Postgres specific extension of SQL standard DISTINCT), taking the highest rank first and, within same rank, the highest peer count. See:
Select first row in each GROUP BY group?

Combining GROUP BY and DISTINCT ON this way in one query level is possible since DISTINCT or DISTINCT ON are applied after GROUP BY.

Mysql – Selecting multiple columns from multiple rows in one column (subquery?)

You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT

SELECT CONCAT(id,' # ',name,' # ',citizens) listing FROM
(
    SELECT A.id,A.name,GROUP_CONCAT(B.name,' ',B.surname) citizens
    FROM city A INNER JOIN citizen B
    ON A.id = B.city_id GROUP BY A.id,A.name
) AA;

Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/9f4b3/1

If the names become too long due to truncation, you will have to extend GROUP_CONCAT's maximum length. To set it to 16K, run the following:

SET group_concat_max_len = 1024 * 16;
SET GLOBAL group_concat_max_len = 1024 * 16;

First line is for the session, the second is for all new DB Connections.

Best Answer

Related Solutions

Postgresql – GROUP BY and ORDER BY problem

Mysql – Selecting multiple columns from multiple rows in one column (subquery?)

Related Question