How to create dynamic columns from the value rows in MySQL

Question

This is my table:

+----+-------+-----------+-------+
| id | Name  | Parameter | Value |
+----+-------+-----------+-------+
|  1 | Andy  | height    | 1.85  |
|  2 | Igal  | weight    | 56    |
|  3 | Yossi | age       | 28    |
|  4 | David | weight    |       |
|  5 | Igal  | height    | 1.9   |
|  6 | Andy  | age       | 25    |
+----+-------+-----------+-------+

This is my query:

SELECT
Name,
CASE WHEN (Paramter='age') THEN Value ELSE 0 END AS AGE,
CASE WHEN (Paramter='height') THEN Value ELSE 0 END AS HEIGHT,
CASE WHEN (Paramter='weight') THEN Value ELSE 0 END AS WEIGHT
FROM
table

The result:

+-------+-----+--------+--------+
| Name  | AGE | HEIGHT | WEIGTH |
+-------+-----+--------+--------+
| Andy  |   0 | 1.85   |      0 |
| Igal  |   0 | 0      |     56 |
| Yossi |  28 | 0      |      0 |
| David |   0 | 0      |        |
| Igal  |   0 | 1.9    |      0 |
| Andy  |  25 | 0      |      0 |
+-------+-----+--------+--------+

I also tried make a group by [Name], but without success.

How do I display all data in one row for each person (Name)?

Wanted table:

+------+-----+--------+--------+ | Name | AGE | HEIGHT | WEIGTH | +------+-----+--------+--------+ | Igal | 0 | 1.9 | 56 | | Andy | 25 | 1.85 | 0 | +------+-----+--------+--------+

The accepted answer and other high ranking answers on the proposed duplicates are rather too much for what this question is asking. — Gordon Linoff, Oct 26 '17 at 18:37
This operation is called pivoting and has been asked and answered many time here on SO. The most upvoted answer in the linked duplicate topic provides you a step-by-step description on how to design such queries in MySQL. However, please note, that it may be more efficient to perform this transformation in the application logic, as opposed to MySQL. — Shadow, Oct 26 '17 at 18:37
`Use `GROUP_CONCAT` and `IF` condition to do that, like in this example: SELECT `Name`, GROUP_CONCAT(IF(`Parameter`='age',`Value`,NULL)) AS age, GROUP_CONCAT(IF(`Parameter`='height',`Value`,NULL)) AS height, GROUP_CONCAT(IF(`Parameter`='weight',`Value`,NULL)) AS weight FROM `table` GROUP BY `Name` //Output: Name age height weight Andy 25 1.85 NULL David NULL NULL Igal NULL 1.9 56 Yossi 28 NULL NULL` — ferhado, Oct 26 '17 at 18:56

Gordon Linoff · Answer 1 · 2017-10-26T18:38:05.527

0

You want some aggregation there:

SELECT Name,
       MAX(CASE WHEN Parameter = 'age' THEN Value END) AS AGE,
       MAX(CASE WHEN Parameter = 'height' THEN Value END) AS HEIGHT,
       MAX(CASE WHEN Parameter = 'weight' THEN Value END) AS WEIGHT
FROM table
GROUP BY Name;

I want to point out the lack of ELSE 0. I see no reason to insert a 0 value for these three columns if no data is present.

edited Oct 26 '17 at 18:38

answered Oct 26 '17 at 18:36

Gordon Linoff

1,242,037
58
646
786

1

Or `SUM`, depending on the desired behavior, but the idea is aggregation in general. – kchason Oct 26 '17 at 18:37
4

Removing the duplicate hammer and then providing the same code logic as described in the duplicate topic is not a really nice behaviour. You have answered this question many times over and over again, you could have chosen any of them as a dupe target if you do not agree with the one I proposed. – Shadow Oct 26 '17 at 18:39
thank you very much. It solved. I missed Max aggregation – ISTech Oct 26 '17 at 18:56
@Shadow . . . You changed the duplicate answer. The current one is much more relevant to this question (the dynamic pivots in SQL Server didn't seem relevant in the one you originally assigned). However, you did that *after* I answered this question. – Gordon Linoff Oct 26 '17 at 20:55
Nope, I have not changed the dupe answer and the one I linked was not about dynamic pivoting. – Shadow Oct 26 '17 at 21:37

How to create dynamic columns from the value rows in MySQL

1 Answers1