Geek asks and answers

SELECT different values ​​for multiple lines of the same identifier

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 a unique identifier and values ​​as named columns so that it looks like this:

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

+5
source share
5 answers

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'

To be safe, I made connections LEFT JOINbecause I don’t know if id can have missing fields, in which case they will appear as NULLin our derived columns.

SQL Fiddle Demo

+5
source

You can also try to perform a rotation using grouping and conditional aggregation:

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
;
+5

:

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')

, NULL . , . , , , , . , .

+2

, MySQL Pivot/Unpivot. :

SELECT a.ID, 
       c.FIELD_NAME AS SIGN_UP,
       a.FIELD_NAME AS FIRST_NAME,
       b.FIELD_NAME AS STREET

  FROM <YOUR-TABLE> a LEFT JOIN <YOUR-TABLE> b
        ON a.ID = b.ID 
     AND a.FIELD_NAME = 'first_name'
     AND b.FIELD_NAME = 'street' LEFT JOIN <YOUR-TABLE> c
        ON c.ID = a.ID
     AND c.FIELD_NAME = 'sign_up'
0

,

SELECT t.id
     , (SELECT f1.value FROM mytable f1 
         WHERE f1.id = t.id AND f1.field_name = 'sign_up' 
         ORDER BY f1.value LIMIT 1
       ) AS SIGN_UP
     , (SELECT f2.value FROM mytable f2 
         WHERE f2.id = t.id AND f2.field_name = 'first_name' 
         ORDER BY f2.value LIMIT 1
       ) AS FIRST_NAME
     , (SELECT f3.value FROM mytable f3 
         WHERE f3.id = t.id AND f3.field_name = 'street'
         ORDER BY f3.value LIMIT 1
       ) AS STREET
  FROM (SELECT s.id
          FROM mytable s
         GROUP BY s.id
         ORDER BY s.id
       ) t

, , , , .

, , _ "" ( NULL ). , . ( .)

, :

SELECT t.id          AS ID
     , f1.sign_up    AS SIGN_UP
     , f2.first_name AS FIRST_NAME
     , f3.street     AS STREET       
  FROM (SELECT s.id
          FROM mytable s
         GROUP BY s.id
         ORDER BY s.id
       ) t
   LEFT      
   JOIN (SELECT s1.id
              , MIN(s1.value) AS sign_up
           FROM mytable s1
          WHERE s1.field_name = 'sign_up'
            AND s1.value IS NOT NULL 
          GROUP BY s1.id
        ) f1
     ON f1.id = t.id   
   LEFT
   JOIN (SELECT s2.id
              , MIN(s2.value) AS first_name
           FROM mytable s2
          WHERE s2.field_name = 'first_name'
            AND s2.value IS NOT NULL
          GROUP BY s2.id
        ) f2
     ON f2.id = t.id
   LEFT
   JOIN (SELECT s3.id
              , MIN(s3.value) AS street
           FROM mytable s3
          WHERE s3.field_name = 'street'
            AND s3.value IS NOT NULL
          GROUP BY s3.id
        ) f3
     ON f3.id = t.id

With other queries, make sure that you get the desired behavior when it is field_name"absent" for a given identifier or if there is a duplicate for this identifier field_nameor when the field contains additional values ​​for the field_name table that you are not associated with.

0
source

More articles:


All Articles