SELECT distinct values for multiple rows of same ID

12,365

Solution 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

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

Solution 3

Adapted from another answer by me:

SELECT ids.ID AS ID,
       sign_up.VALUE AS SIGN_UP,
       first_name.VALUE AS FIRST_NAME,
       street.VALUE AS STREET
FROM (SELECT DISTINCT ID FROM tableName) AS ids
     LEFT JOIN tableName AS sign_up
            ON (sign_up.ID = ids.ID AND
                sign_up.FIELD_NAME = 'sign_up')
     LEFT JOIN tableName AS first_name
            ON (first_name.ID = ids.ID AND
                first_name.FIELD_NAME = 'first_name')
     LEFT JOIN tableName AS street
            ON (street.ID = ids.ID AND
                street.FIELD_NAME = 'street')

The left joins will ensure that missing values will result in NULL cells, instead of an omission of the whole row. Not sure whether that is important in your application. If it is not, you can use an inner join and in particular get rid of the subquery to select all unique IDs. See my original answer from which I derived this.

Share:
12,365
Admin
Author by

Admin

Updated on June 11, 2022

Comments

  • Admin
    Admin 6 months

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