CODEWITHSUNDEEP
sqlcoalesce
user1399137
user1399137

Reputation: 49

SQL coalesce Null columns

select 
    id
 , coalesce(iff(name = 'Alpha', price, null), null) alpha_price
 ,null as beta_price
 , coalesce(iff(name = 'beta', price, null), null) beta_price
  from table1

Input table :

ID     name     price
123   alpha      29
123   beta       40

output :

id.     alpha_price     beta_price 
123       29             NULL
123       NULL           40

expected output :

id     alpha_price     beta_price 
123       29             40

Upvotes: 0

Views: 95

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

Use conditional aggregates (case expressions inside aggregate functions) direct from the input table should go straight to the desired result:

SELECT
      id
    , MAX(case when name = 'alpha' then price end) AS alpha_price
    , MAX(case when name = 'beta' then price end) AS beta_price
FROM input_table
GROUP BY id

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

I think you want to aggregate here:

SELECT id, MAX(alpha_price) AS alpha_price, MAX(beta_price) AS beta_price
FROM table1
GROUP BY id;

This works, at least on the sample data you did show, because the MAX function will by default ignore NULL values.

Upvotes: 1

Related Questions