CODEWITHSUNDEEP
sqlamazon-redshift
Nonagon
Nonagon

Reputation: 407

SQL Redshift: How to use a value in a join based on another columns value

i have a table with columns such as this:

name   product    existing_customer
john   crisps     yes

paul   cheese     no

george broccoli   no

ringo  spam       yes

but in order to populate this table as such I'm referencing this layout:

name       value.      tag
john       crisps.     product
john       yes.        existing_customer
george     broccoli.   product
george     no.         existing_customer
etc        etc

I need the output like the first table, I have tried it with case statements but it is showing multiple lines with the same name and showing each value separately.

Is there a way to do this in redshift SQL?

Upvotes: 1

Views: 724

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

You can use aggregation:

select name,
       max(case when tag = 'product' then value end) as product,
       max(case when tag = 'existing_customer' then value end) as existing_customer
from t
group by name;

Upvotes: 1

Vesa Karjalainen
Vesa Karjalainen

Reputation: 1105

With the new input, something like:

select * from 
( select name, value as product 
  from in_values 
  where tag='product') as p
full outer join 
( select name, value as existing  
  from in_values 
  where tag='existing_customer') as e 
using (name);

Upvotes: 0

Related Questions