CASE WHEN query not returning expected results
<p>I am trying to get data from the wp_usermeta table of the Wordpress database.</p>

<p>For those who aren't familliar with it, the structure is as follows:</p>

<pre><code>umeta_id | user_id | meta_key | meta_value
1        | 1       | role     | admin
2        | 1       | lat      | 12.34456
3        | 1       | lng      | 9.392933
4        | 2       | role     | user
5        | 2       | lat      | 8.694840
6        | 2       | lng      | 29.32930
7        | 3       | role     | subscriber
8        | 3       | lat      | 32.32323
9        | 3       | lng      | 3.222222
10       | 3       | some key | some value
... and so on
</code></pre>

<p>Now I've got an entry with latitude and longitude coordinates inside the table, which are defined by the meta_key being "lat" or "long" and the meta_value being the coordinates.</p>

<p>I'd like to get each user with their lat and long coords, like this:</p>

<pre><code>user_id | lat      | lng
1       | 12.34456 | 9.392933
2       | 8.694840 | 29.32930
3       | 32.32323 | 3.222222
</code></pre>

<p>For that I am currently using this query:</p>

<pre><code>SELECT umeta.user_id,
max(CASE WHEN umeta.meta_key = "lat" THEN umeta.meta_value END) AS lat,
max(CASE WHEN umeta.meta_key = "lng" THEN umeta.meta_value END) AS lng
FROM wp_usermeta umeta
</code></pre>

<p>Which only gives me:</p>

<pre><code>user_id | lat      | lng
1       | 12.34456 | 9.392933
</code></pre>

<p>Do you guys have any idea what I am doing wrong?
Help really appreciated!</p>