Skip to content
Advertisement

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.

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;

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement