SELECT distinct values for multiple rows of same ID

Question

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

Answer 1

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

Answer 2

You could also try pivoting with the help of grouping and conditional aggregating:

SELECT
  ID,
  MAX(CASE FIELD_NAME WHEN 'sign_up'    THEN VALUE END) AS SIGN_UP,
  MAX(CASE FIELD_NAME WHEN 'first_name' THEN VALUE END) AS FIRST_NAME,
  MAX(CASE FIELD_NAME WHEN 'street'     THEN VALUE END) AS STREET
FROM atable
GROUP BY
  ID
;