How to convert column heading to column values and its values in another column in postgresql

user_7264802 0
Viewed 36

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.

Amazon Flipkart Myntra
5 4 3

I want to convert it to following format

Website name Count
Amazon 5
Flipkart 4
Myntra 3.

How can I achieve this?

Thanks in Advance!

transpose unpivot postgresql sql
2 Answers

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;
user_153326500

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

user_138083190
Related