1

I'm trying to join 2 tables together and seperate a column into multiple columns based on an id tag.

e.g Table "Name"

id | field_number | value |
---|--------------|------ |
1  |     1        | john  |
1  |     2        | murphy|
2  |     1        | mary  |
2  |     2        | murphy|

Table "id"

id ¦ user
1  ¦ person1
2  ¦ person2

Result

id ¦ First Name ¦ Last Name ¦ user
 1 ¦    john    ¦ murhpy    ¦ person1
 2 ¦    mary    ¦ murphy    ¦ person2

Is there a simple way of doing this?

sagi
  • 40,026
  • 6
  • 59
  • 84
Eric Larson
  • 13
  • 3
  • http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 – e4c5 Dec 06 '16 at 11:31

1 Answers1

0

Use conditional aggregation :

SELECT t.id,
       MAX(CASE WHEN s.field_number = 1 THEN s.value END) as first_name,
       MAX(CASE WHEN s.field_number = 2 THEN s.value END) as last_name,
       t.user
FROM `id` t
JOIN `name` s
 ON(t.id = s.id)
GROUP BY t.id,t.user
sagi
  • 40,026
  • 6
  • 59
  • 84