mysql pivot retrieving strings

Question

Simple question: what is the correct way to retrieve a string from a custom pivot table. Is there a better way than MAX(CASE WHEN _ad.key = 'first_name' THEN _ad.value ELSE '' END) AS first_name

My table works fine, my question is how to retrieve a single string, every question I've found is asking how to aggregate rows to find the sum of a bunch of rows. Is there a better solution for my case? As there will only ever be one value returned, it will always be the maximum value.

dev.mysql.com: "MAX() may take a string argument; in such cases, it returns the maximum string value."

Here is a minimised version of my query.

SELECT
    _a.id_account,
    MAX(CASE WHEN _ad.`key` = 'first_name' THEN _ad.`value` ELSE '' END) AS first_name,
    MAX(CASE WHEN _ad.`key` = 'last_name' THEN _ad.`value` ELSE '' END) AS last_name

FROM
    `account` _a
        LEFT JOIN account_data _ad USING(id_account)

GROUP BY
    _a.id_account;



|----------------------------------------
|account
|----------------------------------------
|id_account  |
|1           |
|2           |
|----------------------------------------

|----------------------------------------
|account_data
|----------------------------------------
|id_account  |key           |value
|1           |first_name    |OneFirst
|1           |last_name     |OneLast
|2           |first_name    |TwoFirst
|----------------------------------------

|----------------------------------------
|mypivot
|----------------------------------------
|id_account  |first_name    |last_name
|1           |OneFirst      |OneLast
|2           |TwoFirst      |
|----------------------------------------

One thing that might be helpful is that if you have many values for key you can use dynamic pivot to let mysql build all those `MAX(CASE WHEN..` for you,like http://stackoverflow.com/a/28284999/1745672 — Mihai, Dec 20 '15 at 22:42
@Mihai Thanks for the reference it might be helpful in the future, for this query I was trying to avoid subqueries. — Bradmage, Dec 22 '15 at 00:03

score 2 · Accepted Answer · answered Dec 19 '15 at 03:50

2

I believe your method is pretty good and I would have written it the same way you did.

With pivoting, you will have to group by your pivot id_account. First name can be derived by max of first_name key if it exists. You did that, and you repeated that with last_name. That's very good, and it yields you the right results. So you are good to go, from what I see.

answered Dec 19 '15 at 03:50

zedfoxus

35,121
5
64
63

Sweet thanks. I was half expecting a whole heap of people harassing me for storing data that way and not answering my question, so thank :) – Bradmage Dec 19 '15 at 04:50

score -1 · Answer 2 · answered Dec 20 '15 at 22:35

-1

Give this a try:

IFNULL(MAX(first_name), '')

answered Dec 20 '15 at 22:35

Rick James

135,179
13
127
222

This is in the low quality posts queue. Please edit your answer and explain why this works. – Rohit Gupta Dec 21 '15 at 00:25
I agree, this query doesn't do anything. your haven't created `first_name` to test against first. – Bradmage Dec 21 '15 at 03:01

mysql pivot retrieving strings

2 Answers2