I'm trying to extract data from a SQLite table that stores key-value pairs in dual columns. For example, with the keys foo , bar , man , and row , the table would look like:

| _id | external_id | key | value | |-----|-------------|------|-------| | 1 | 12345 | foo | cow | | 2 | 12345 | bar | moo | | 3 | 12345 | man | hole | | 4 | 12345 | row | boat | | 5 | 67980 | foo | abc | | 6 | 67890 | bar | def | | 7 | 67890 | man | ghi | | 8 | 67890 | row | jkl |

I want to perform a query that gives me each external_id in a row with the keys as the columns and the values as the rows. Like this:

| external_id | foo | bar | man | row | |-------------|-----|------|------|------| | 12345 | cow | moo | hole | boat | | 67890 | abc | def | ghi | jkl |

The only solution I've been able to come up with is a join for each key:

SELECT a.external_id, b.foo, c.bar, d.main, e.row 
FROM myTable AS a
LEFT JOIN 
  (SELECT external_id, key AS foo
   FROM myTable
   WHERE key="foo") AS b
   ON a.external_id = b.external_id
LEFT JOIN 
  (SELECT external_id, key AS row
   FROM myTable
   WHERE key="row") AS e
   ON a.external_id = e.external_id
GROUP BY a.external_id
Is there a better way to do this?
                One of the many reasons that the EAV model is generally a horrible idea in SQL. /soapbox
                    – Tom H
                Feb 18 '16 at 15:17
SELECT external_id,
       MAX(CASE WHEN key = 'foo' THEN value END) AS foo,
       MAX(CASE WHEN key = 'bar' THEN value END) AS bar,
       MAX(CASE WHEN key = 'man' THEN value END) AS man,
       ... etc
FROM mytable 
GROUP BY external_id 
                That's perfect, thanks! I never would have thought of using the aggregate functions like this.
                    – hairlessbear
                Feb 18 '16 at 15:26
                This is absolutely incredible. Never thought of it either, but is immensely helpful to anyone faced with this "property-bag" pattern (ahem Wordpress ahem).
                    – Astockwell
                Oct 29 '18 at 21:27
collected_objects['foo'] as foo,
collected_objects['bar'] as bar,
collected_objects['man'] as man,
collected_objects['row'] as row
from(
  select external_id,
  collect(key, value) as collected_objects
  group by external_id)t1
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
                    
Related
                        
2500Add a column with a default value to an existing table in SQL Server
533Fetch the row which has the Max value for a column
1677How to concatenate text from multiple rows into a single text string in SQL server?
1529Inserting multiple rows in a single SQL query?
561Sqlite primary key on multiple columns
875Using group by on multiple columns
1603Finding duplicate values in a SQL table
1053Select first row in each GROUP BY group?
1034SQL select only rows with max value on a column
1To use SELECT query in WHERE clause
site design / logo © 2019 Stack Exchange Inc; user contributions licensed under cc by-sa 3.0
                            with attribution required.
                    rev 2019.4.29.33475