Presto Pivoting Data

Question

I am really new to Presto and having trouble pivoting data in it. The method I am using is the following:

select
distinct location_id,
case when role_group = 'IT' then employee_number end as IT_emp_num,
case when role_group = 'SC' then employee_number end as SC_emp_num,
case when role_group = 'HR' then employee_number end as HR_emp_num
from table
where 1=1
and id = 1234

This is fine, however, null values are also populated for the rows and I would like to pivot the data, to only return one row with the relevant info.

I have tried using the array_agg function, which will collapse the data but it also keeps the null values (e.g. it will return null,301166,null for the first colum)

Answer 1

If only one row per location is needed you can use max with group by:

select location_id, 
  max(IT_emp_num) IT_emp_num, 
  max(SC_emp_num) SC_emp_num, 
  max(HR_emp_num) HR_emp_num
from (
  select location_id,
    case when role_group = 'IT' then employee_number end as IT_emp_num,
    case when role_group = 'SC' then employee_number end as SC_emp_num,
    case when role_group = 'HR' then employee_number end as HR_emp_num
  from table
  where id = 1234)
group by location_id