How best can I to pivot my redshift table?

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.

Answer

You can use conditional aggregation. Assuming you know the columns you want in the result set, you can use:

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 t
group by id;

If you don’t know the specific values and need to read them from the data . . . that is trickier. As far as I know, Redshift doesn’t support dynamic SQL. So, you would need to query the database for the different key values and construct a query as a string using your favorite language. You can then execute that query.