snowflake-cloud-data-platformpivot

Pivot with Max aggregate function with null values is not working in Snowflake


Need help below is the query , but table t3 is blank , which is giving me error in snowflake i.e unexpected 'SEP'.

select x48.SEP from
(
  (
  SELECT * FROM 
  (SELECT EID
  ,  month11
  , MIN(date_id) date_id
    FROM   t3  ---this table is empty 
    WHERE missing_mn >= 48
    GROUP BY EID, month11
    ) PIVOT (MAX(date_id) FOR month11 IN  ('SEP' SEP , 'OCT' OCT , 'NOV' NOV, 'DEC' DEC, 'JAN' JAN, 'FEB' FEB, 'MAR' MAR , 'APR' APR, 'MAY'  MAY
      )
    )
  )x48
)

T3 table with sameple data: EID = 1122345 ,month11 = SEP,MAR,APR etc .. , date_id = 20250101 or 20250201 (date to number )


Solution

  • The error you're getting has nothing to do with t3 being empty. You've got a syntax error in your pivot clause; by default, Snowflake encloses the pivoted column names with ', and therefore, you have to put them in double quotes ":

    with t3(eid, month11, date_id, missing_mn) as (
      select 2, 'SEP', '2025-09-14', 50  where 1=0
      
    )
    select x48."'SEP'"
      from ((select 
               from (select EID,
                            month11,
                            min(date_id) date_id
                       from t3 ---this table is empty 
                      where missing_mn >= 48
                      group by EID,
                               month11)
             PIVOT(max(date_id)
                for month11 in('SEP',
                               'OCT',
                               'NOV',
                               'DEC',
                               'JAN',
                               'FEB',
                               'MAR',
                               'APR',
                               'MAY'))) x48)
    

    If you'd rather use aliases, you can define them like this:

    with t3(eid, month11, date_id, missing_mn) as (
      select 2, 'SEP', '2025-09-14', 50  where 1=0  
    )
    select x48.sep
      from ((select *
               from (select EID,
                            month11,
                            min(date_id) date_id
                       from t3 ---this table is empty 
                      where missing_mn >= 48
                      group by EID,
                               month11)
             PIVOT(max(date_id)
                for month11 in('SEP',
                               'OCT',
                               'NOV',
                               'DEC',
                               'JAN',
                               'FEB',
                               'MAR',
                               'APR',
                               'MAY')) as p(dat, sep, oct, nov, dec, jan, feb, mar, apr, may)) x48)
    
    

    see also Snowflake Pivot Without Apostrophe