Unpivot columns into rows in SQL

Question

I have a table similar as follows:

  date       customer      shop       view       add        buy     
01/01/21      tim          abc         10         5          1             
01/01/21      anna         abc          2         2          2             
02/01/21      anna         hnm          5         4          3             

My desired output is as the table below:

  date       customer      shop       activity       value           
01/01/21      tim          abc         view           10           
01/01/21      tim          abc         add            5        
01/01/21      tim          abc         buy            1     
01/01/21      anna         abc         view           2           
01/01/21      anna         abc         add            2        
01/01/21      anna         abc         buy            2                   
02/01/21      anna         hnm         view           5           
02/01/21      anna         hnm         add            4        
02/01/21      anna         hnm         buy            3   

I would like to unpivot the table, but I'm not sure what would be the best way to do it? Is UNNEST() the right approach to do this? Here is the query that I tried but it did not work:

SELECT date,
       customer, 
       shop,
       UNNEST(ARRAY['view', 'add', 'buy']) AS activity
       UNNEST(ARRAY[view, add, buy]) AS value
FROM table 
GROUP BY date, customer, shop 

Any suggestions that you could give me would be greatly appreciated.

Answer 1

In Redshift, union all might be the simplest method:

select date, customer, shop, 'view' as activity, view as value 
from t
union all
select date, customer, shop, 'add' as activity, add as value 
from t
union all
select date, customer, shop, 'buy' as activity, buy as value 
from t;

You can also unpivot with case and cross join:

select t.date, t.customer, t.shop, x.activity,
       (case x.activity when 'view' then t.view when 'add' then t.add when 'buy' then t.buy end) as value
from t cross join
     (select 'view' as activity union all
      select 'add' as activity union all
      select 'buy' as activity
     ) x;

Note that aggregation is not necessary.