CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
Ben Shichman
Ben Shichman

Reputation: 5

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

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.

Upvotes: 0

Views: 28

Answers (1)

Popeye
Popeye

Reputation: 35900

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;

Upvotes: 1

Related Questions