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.

Answer 1

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