Transpose 1 row into columns with count/sum

Question

I have a a single row with age buckets as column names. I want them all in one column with their count or sum whatever I end up doing. I have quite a few buckets but for the sake of this example I've kept it simple. Please see the attached picture of the data and the outcome that is needed.

Please provide data as *text*. Best as usable test case. Never as image. And always your version of Postgres. — Erwin Brandstetter, Aug 06 '22 at 00:52

score 1 · Accepted Answer · answered Aug 06 '22 at 00:59

A VALUES expression in a LATERAL join is among the most elegant solutions:

SELECT x.*
FROM   tbl t
CROSS  JOIN LATERAL (
   VALUES
     ('age5' , t.age5)
   , ('age10', t.age10)
   , ('age15', t.age15)
   , ('age20', t.age20)
   ) x (bucket_name, bucket_value);

See:

SELECT DISTINCT on multiple columns

Transpose 1 row into columns with count/sum

1 Answers1