Julia Julia - 2 months ago 12
SQL Question

Pivoting column Postgresql

I have a table that looks like this:

username item_name total_units_per_username
abc@gma.com laptop 2
abc@gma.com watch 2
xyz@gma.com phone 3
xyz@gma.com laptop 3
xyz@gma.com watch 3


what I would like is a table that looks like this:

total_units_per_username item_name frequency
3 phone, laptop, watch 1


Essentially I want to pivot the item_name column, concatenate all the values over the total_units_per_username, and count the frequency of that occurrence. I'm using Snowflake.

Thanks!

Answer

I think you want two aggregations:

select numitems, items, count(*) as freq
from (select username,
             string_agg(item_name order by item_name, ', ') as items,
             count(*) as numitems
      from t
      group by username
     ) t
group by numitems, items;

EDIT:

You can also do this with array_agg():

select numitems, items, count(*) as freq
from (select username,
             array_agg(item_name order by item_name) as items,
             count(*) as numitems
      from t
      group by username
     ) t
group by numitems, items;