Combine key and value columns and one row in MYSQL

Question

I want to combine multiple values just by doing a select. I have this:

| id | student_id | key       | value     |
| 1  | 1          | dad_name  | Leonel    |
| 2  | 1          | mom_name  | Mireya    |
| 3  | 1          | school    | Agape     |

And to convert it to this:

| id | student_id | dad_name | mom_name | school |
| 1  | 1          | Leonel   | Mireya   | Agape  |

Why can't you do this in your application layer? Doing it in MySQL is a mess. — tadman, Oct 01 '17 at 21:02
This is a MySQL pivot table. https://stackoverflow.com/questions/7674786/mysql-pivot-table — Willy Richardson, Oct 01 '17 at 21:02

score 1 · Answer 1 · answered Oct 01 '17 at 21:08

One way (though admittedly not the best way) would be:

SELECT r.id, r.student_id, r.value as dad_name, r2.value as mom_name, r3.value as school 
FROM table r 
     JOIN table r2 ON r.student_id=r2.student_id 
     JOIN table r3 ON r.student_id=r3.student.id 
WHERE r.key='dad_name' 
      AND r2.key='mom_name' 
      AND r3.key='school'

score 1 · Answer 2 · answered Oct 01 '17 at 21:10

1

You can just join the table with itself. Let's $table is the name of your table; then the query will look somethin like this

"
SELECT t1.student_id, t2.value AS dad_name, 
  t3.value AS mom_name, t4.value AS school 
FROM $table AS t1 LEFT JOIN ($table AS t2, $table AS t3, $table AS t4)
ON (t2.student_id = t1.student_id AND t3.student_id = t1.student_id 
  AND t4.student_id = t1.student_id)
"

answered Oct 01 '17 at 21:10

jh1711

2,288
1
12
20

Not sure the `LEFT JOIN` is necessary for self joins like this one. – apokryfos Oct 02 '17 at 07:49

score 0 · Answer 3 · answered Oct 01 '17 at 21:17

This isn't strictly what you asked for, but as per tadman's comment, it would possibly be cleaner to do this kind of transformation on the application layer.

For example...

$output = [];
foreach($resultset as $row) {
    $output[$row->student_id]->{$row->key} = $row->val;
}

This would result in an output like this

Array
(
    [1] => stdClass Object
        (
            [dad_name] => Leonel
            [mom_name] => Mireya
            [school_name] => Agape
        )

)

Combine key and value columns and one row in MYSQL

3 Answers3