title: mysql pivot retrieving strings
body: <p><strong>Simple question:</strong> what is the correct way to retrieve a string from a custom pivot table. Is there a better way than <code>MAX(CASE WHEN _ad.key = 'first_name' THEN _ad.value ELSE '' END) AS first_name</code></p>
  
  <p>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.</p>
  
  <p>dev.mysql.com: "MAX() may take a string argument; in such cases, it returns the maximum string value."</p>
  
  <p>Here is a minimised version of my query.</p>
  
  <pre><code>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      |
  |----------------------------------------
  </code></pre>