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 stackoverflow.com/a/28284999/1745672 — Mihai
– Mihai, Commented Dec 20, 2015 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
– Bradmage, Commented Dec 22, 2015 at 0:03

zedfoxus · Accepted Answer · 2015-12-19 03:50:05Z

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, 2015 at 3:50

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bradmage Over a year ago

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 :)

Rick James · Accepted Answer · 2015-12-20 22:35:58Z

-1

Give this a try:

IFNULL(MAX(first_name), '')

answered Dec 20, 2015 at 22:35

Rick James

144k15 gold badges144 silver badges254 bronze badges

2 Comments

Rohit Gupta Over a year ago

This is in the low quality posts queue. Please edit your answer and explain why this works.

Bradmage Over a year ago

I agree, this query doesn't do anything. your haven't created first_name to test against first.

Collectives™ on Stack Overflow

mysql pivot retrieving strings

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related