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.

https://stackoverflow.com/questions/65837669/how-best-can-i-to-pivot-my-redshift-table January 22, 2021 at 08:22AM