How to transpose text values from one column into row values with iterated column name in SNOWFLAKE

Question

I am trying to take values from one column (i.e. tasted bad, dirty bathroom, etc) and then turn it into a row with headeres being (reason 1, reason 2, etc). The PIVOT function seems to require an agg function and it only works on numbers. There is an ID column and a timestamp column. There can be multiple reasons per ID . Every example out there seems to use numbers

I've tried the below code and got all nulls for values

select * from Reason_Dedup
pivot(SUM(Event_Desc) FOR Event_Desc in ('Reason 1', 'Reason 2', 'Reason 3', 'Reason 4', 'Reason 5', 'Reason 6', 'Reason 7'))
      as p
      order by agreement_id, create_date

In SAS I would do this:

proc sort data= Reason_Dedup out=longsort;
    by  agreement_id descending create_date;
run;

proc transpose data=longsort out=wide1 Prefix=reason;
    by agreement_id;
    var Event_Desc;
run;

Answer 1

You can start by numbering the reasons of each agreement with row_number, then pivot. I would recommend conditional aggregation instead of the vendor-specific pivot syntax. It is standard SQL, and gives more flexibility in general:

select agreement_id, 
    max(case when rn = 1 then event_desc end) reason_1,
    max(case when rn = 2 then event_desc end) reason_2,
    max(case when rn = 3 then event_desc end) reason_3
from (
    select r.*, 
        row_number() over(partition by agreement_id order by create_date) rn
    from reason_dedup r
) r
group by agreement_id