I have a table like this say the table name is online-websites and the values it has is the number of times I used it.

I want to convert it to following format

How can I achieve this?

Thanks in Advance!

CodePudding user response：

for your specific example one common way to unpivot is to lateral-join to a values-table construct:

select p.*
from t,
lateral (
  values
  ('Amazon', t.Amazon),
  ('Flipkart', t.Flipkart),
  ('Myntra', t.Myntra)
)p(SiteName, count)
order by count desc;

CodePudding user response：

In the bizarre case that you do not know the number of columns beforehand, you can use jsonb functions to pivot unknown columns to rows.

select e.*
  from online_websites
       cross join lateral jsonb_each_text(
                            to_jsonb(online_websites)
                          ) as e(website_name, "count");

db<>fiddle here