SELECT distinct values for multiple rows of same ID
mysql, sql
Solution
You can use this simple solution:
SELECT DISTINCT
a.id,
b.value AS SIGN_UP,
c.value AS FIRST_NAME,
d.value AS STREET
FROM tbl a
LEFT JOIN tbl b ON a.id = b.id AND b.field_name = 'sign_up'
LEFT JOIN tbl c ON a.id = c.id AND c.field_name = 'first_name'
LEFT JOIN tbl d ON a.id = d.id AND d.field_name = 'street'
Just to be safe, I made the joins `LEFT JOIN`'s because I do not know if an id can have missing fields, in which case they will show up as `NULL` in our derived columns.
SQL-Fiddle Demo
Problem
I have a table that looks like this: ``` ID | FIELD_NAME | VALUE 23 | sign_up | yes 23 | first_name | Fred 23 | street | Barber Lane 24 | sign_up | no 24 | first_name | Steve 24 | street | Camaro St. 25 | sign_up | yes 25 | first_name | Larry 25 | street | Huckleberry Ave ``` I want to run a query that will select unique ID's and the values as named columns so it would appear like so: ``` ID | SIGN_UP | FIRST_NAME | STREET | 23 | yes | Fred | Barber Lane | 24 | no | Steve | Camaro St. | 25 | yes | Larry | Huckleberry Ave. | ``` Any help would be much appreciated!!