Ability to extract IDs from one column and return them in their own columns using SQL

Question

I have a column in a table that holds multiple identifier values in it. One of the other columns in the table identifies the ID type that the values are associated to. There is also a key ID column in the table (which is another ID value). What I want to accomplish is to identify a list of the key Ids in the where statement and based on that return 2 different ID values as their own columns (based on the Id_type as the header) along with the key id. See below

Table: external_ids

| id_type    | identifier     | key_id |
| program.id | 123456         | abcde  |
| partner.id | 5432           | abcde  |
| product.id | 6KWt1Qo04O2M   | abcde  |
| aps        | EP013836200004 | abcde  |
| program.id | 789012         | defghi |
| partner.id | 9876           | defghi |
| product.id | 9bb72eb42a93f  | defghi |
| aps        | EP012795410004 | defghi |

This is the query I am using (I know it doesn't work):

select distinct
       identifier, identifier, key_id
from external_ids
where key_id in ('abcde','defghi')
  and id_type = (select identifier from external_ids
                 where id_type in ('aps','product.id')

My desired output would be:

| aps            | product.id    | key_id |
| EP013836200004 | 6KWt1Qo04O2M  | abcde  |
| EP012795410004 | 9bb72eb42a93f | defghi |

Answer 1

Your current table design is a denormalized key value store. One approach is to aggregate by the key_id, and the pivot out the various id_type keys whose values you wish to report.

SELECT
    key_id,
    MAX(CASE WHEN id_type = 'aps'        THEN identifier END) AS aps,
    MAX(CASE WHEN id_type = 'product.id' THEN identifier END) AS "product.id"
FROM yourTable
GROUP BY
    key_id;