OhFiddyYouSoWiddy OhFiddyYouSoWiddy - 1 year ago 57
MySQL Question

How to transform (or possibly pivot) these MySQL results into a consolidated view?

The result of a MySQL query look like this:

key | name | value
1 | foo | alpha
1 | bar | beta
2 | foo | charlie
2 | bar | delta
3 | foo | echo

How can I transform the result into this?

key | foo | bar
1 | alpha | beta
2 | charlie | delta
3 | echo |

I would prefer a SQL solution, but the result is being passed into PHP so I could manipulate the result using PHP if there is no SQL solution. I am reluctant to use PHP however as I have a feeling it will be very slow for queries where there may be a thousand or more different values in the

I have heard the term pivoting being used when I searched for an answer on Google. I don't fully understand what that means though so I'm not sure if pivoting is relevant here.

Thanks in advance for any suggestions.

Answer Source

Just use conditional aggregation:

select `key`,
       max(case when name = 'foo' then value end) as foo,
       max(case when name = 'bar' then value end) as bar
from t
group by `key`;

If you have multiple values for each key, then group_concat() might be more appropriate than max().

If you don't know the list of possible names, then Google "MySQL dynamic pivot" for examples of what to do. That requires using dynamic SQL.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download