How do i pivot this redshift table? (Revised question)

• 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.

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;