CODEWITHSUNDEEP
mysqlaggregate-functionspivot-table
Bradmage
Bradmage

Reputation: 1231

mysql pivot retrieving strings

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

Upvotes: 3

Views: 1172

Answers (2)

Rick James
Rick James

Reputation: 142518

Give this a try:

IFNULL(MAX(first_name), '')

Upvotes: -1

zedfoxus
zedfoxus

Reputation: 37119

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.

Upvotes: 2

Related Questions