- Note: This is a revised version of another question – i realized i didnt have complete requirements (sorry about that!)
I have a Redshift table called metadata
with the following columns:
id | key | value |
---|---|---|
1001 | code1 | my value |
1001 | code2 | another |
1001 | code3 | yet another |
1002 | code1 | new one |
1002 | code2 | here |
1002 | code3 | last |
1003 | code1 | hello |
1003 | code2 | goodbye |
1004 | code2 | now |
1004 | code3 | then |
I’d like to have a query that return it as:
id | code1 | code2 | code3 |
---|---|---|---|
1001 | my value | another | yet another |
1002 | new one | here | last |
1003 | hello | goodbye | |
1004 | now | then |
Note that not all ID’s will have all 3 codes. Some will have 1 or 2 of them, others all 3, and others still, none.
Advertisement
Answer
Use conditional aggregation:
Select id, Max(case when key = 'code1' then value end) as code1, Max(case when key = 'code2' then value end) as code2, Max(case when key = 'code3' then value end) as code3 From your_table t Group by id;