hairlessbear hairlessbear - 11 days ago 6
SQL Question

Group key-value columns into a single row

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?

Answer

The other available option is to use conditional aggregation:

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