case is generating nulls in SQL databricks-CodePudding

I have a very simple table with key/value pairs in databricks. I want to move from long to wide just a couple of columns not using PIVOT. But I always end with Nulls no matter how i do. (i have been checking this very same SQL in some other databases as mysql and everything worked as expected)

   CREATE TEMP VIEW channels(id, key, value) AS
       VALUES (1,'channel', 10),
              (1,'version',  20),
              (2,'channel',  30),
              (2,'version',  40),
              (3,'channel', 50),
              (3,'version', 60),
              (4,'channel',  70),
              (4,'version',  80),
              (5,'channel', 95),
              (5,'version',  105);
           
          
select max(id) as id,
case when max(key) = "channel" then max(value)  end as channel
from channels
group by id;
          
id  channel
1   NULL    
2   NULL    
3   NULL    
4   NULL    
5   NULL

Expected

id  channel
1   10
2   30
3   50
4   70
5   95

CodePudding user response：

Put the case expression inside the MAX():

select id,
       max(case when key = "channel" then value end) as channel
from channels
group by id;

CodePudding user response：

Using conditional aggregation