MySQL: result of GROUP BY into columns-CodePudding

I have a dogs table that looks like this:

create table dogs (name char(10), breed, char(20), age int);

insert into dogs (name, breed, age) values ('A', 'Breed 1', 1);
insert into dogs (name, breed, age) values ('B', 'Breed 2', 2);
insert into dogs (name, breed, age) values ('C', 'Breed 3', 3);
insert into dogs (name, breed, age) values ('D', 'Breed 1', 4);
insert into dogs (name, breed, age) values ('E', 'Breed 2', 2);
insert into dogs (name, breed, age) values ('F', 'Breed 3', 5);
insert into dogs (name, breed, age) values ('G', 'Breed 1', 1);

I need to select names of dogs for which their respective breeds and ages are equal, so the output should be the following:

name1    name2
A        G
B        E

because dogs A and G have the same breed (Breed 1) and age (1), and dogs B and E have the same breed (Breed 2) and age (2).

I know how to find out the names of the dogs that should be in the output:

select name from dogs where (breed, age) in (select breed, age from (select breed, age, count(*) as n from dogs group by breed, age having n > 1) as a);

However I'm lost as to how to convert it to a desired output with two columns.

CodePudding user response：

That is usually done with a self join:

select D1.name, D2.name
from dogs d1
     inner join
     dogs d2
     on d1.breed=d2.breed
     and d1.age=d2.age
     and d1.name<d2.name

the d1.name>d2.name serves two purposes: 1. to prevent the row to match itself, and 2. to make sure that we get only one row per pair (not (A,G) and (G,A), for example).

CodePudding user response：

UseGROUP_CONCAT() to distinguish diffrent names

select 
GROUP_CONCAT(distinct name) name
from dogs
group by 
breed
,age 
having GROUP_CONCAT(name) like '%,%'

the result is

name
A,G
B,E

then use SUBSTRING_INDEX and subquery to change above result to columns

SELECT 
SUBSTRING_INDEX(Name, ',', 1) name1    
,SUBSTRING_INDEX(Name, ',', -1) name2   
From(
select 
GROUP_CONCAT(distinct name) Name
from dogs
group by 
breed
,age 
having GROUP_CONCAT(name) like '%,%') a

name1	name2
A	G
B	E

dbfiddle

CodePudding user response：

SELECT 
 d1.name,d1.breed, d1.age, d2.name, d2.breed, d2.age
   FROM dogs  d1 
    JOIN dogs  d2
      ON d1.breed=d2.breed AND d1.age = d2.age AND d1.name != d2.name