I'm trying to select a value from a table like the following example:
user id_1 id_2 id_3
-----------------------
a 1 NULL 3
b 1 NULL NULL
c NULL NULL 3
d NULL 2 NULL
I'm using the following pivot to come up with the above result:
select user,
max(case when id = 1 then id end) as id_1,
max(case when id = 2 then id end) as id_2,
max(case when id = 3 then id end) as id_3
from table t
group by user
order by user;
I want to change the Nulls to zeros to have the following result:
user id_1 id_2 id_3
-----------------------
a 1 0 3
b 1 0 0
c 0 0 3
d 0 2 0
I used COALESCE like the following:
select user,
max(case when id = 1 then COALESCE(id,0) end) as id_1,
max(case when id = 2 then COALESCE(id,0) end) as id_2,
max(case when id = 3 then COALESCE(id,0) end) as id_3
from table t
group by user
order by user;
but it didn't work, any advice?
Each CASE statement is evaluating to NULL when the WHEN isn't satisfied. And MAX() returns NULL when all values are NULL. If you simply want to get 0 instead of NULL, put else 0 before each end.
select user,
max(case when id = 1 then id else 0 end) as id_1,
max(case when id = 2 then id else 0 end) as id_2,
max(case when id = 3 then id else 0 end) as id_3
from table t
group by user
order by user;