Group key-value columns into a single row

Question

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?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

Group key-value columns into a single row

Sign up