Question 235756 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – one to many – values become result columns

Assume I have two tables related with one to many:

Person:
  - name
  - age
  - phone

Attributes:
  - fk
  - field
  - value

for example

Person:

id name age phone
1 John 18 null
2 Sue 22 1234567890
3 Linda 31 9876543210

Attributes:

id fk field value
1 1 hair brown
2 1 eyes blue
3 2 hair black
4 3 height 6

Is it possible to create a query with a result that looks like this:

name age phone hair eyes height
John 18 null brown blue null
Sue 22 1234567890 black null null
Linda 31 9876543210 null null 6
Tags:

2

Answers


  1. 0

    Unfortunately MySQL doesn’t support pivot as far as I am aware, but if this does not need to have a dynamic amount of attributes, you could self join Attributes via left join, and filter the joins to the attributes needed:

    select p.name
    , p.age
    , p.phone
    , hair.value
    , eye.value
    , height.value
from Person p
left join Attributes hair
    on p.id = hair.fk
    and hair.field = 'hair'
left join Attributes eye
    on p.id = eye.fk
    and eye.field = 'eye'
left join Attributes height
    on p.id = height.fk
    and height.field = 'height'

    If you do require a dynamic number of attributes, you can create a prepared statement, but I don’t have an example of that readily available.

    Login or Signup to reply.
  2. 0

    Here’s a solution using conditional aggregation.

    create table person (
  id integer, 
  name varchar(10), 
  age integer, 
  phone varchar(10)
  );
  
insert into person values 
(1, 'John', 18, null), 
(2, 'Sue', 22, '1234567890'), 
(3, 'Linda', 31, '9876543210'), 
(4, 'Seymore', 78, null);

create table attributes (
  id integer, 
  fk integer, 
  field varchar(20), 
  value varchar(20)
  );
  
insert into attributes values 
(1, 1, 'hair', 'brown'), 
(2, 1, 'eyes', 'blue'), 
(3, 2, 'hair', 'black'), 
(4, 3, 'height', '6');

    select  
  p.name, 
  p.age, 
  p.phone, 
  max(case when a.field = 'hair' then a.value else null end) as hair, 
  max(case when a.field = 'eyes' then a.value else null end) as eyes, 
  max(case when a.field = 'height' then a.value else null end) as height
from person p
left join attributes a
  on p.id = a.fk
group by p.name, p.age, p.phone;
    name age phone hair eyes height
    John 18 brown blue
    Sue 22 1234567890 black
    Linda 31 9876543210 6
    Seymore 78

    View on DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search