How do I pivot my table to show two separate columns

Question

(my database shows me each individual person and how much of a payout they have and at which stage they have it - f.y.i not every person has all 4 payouts)

I have created a table with averages for different stages called payout1, payout 2, payout 3 and payout 4.

Each stage has a payout amount and there are multiple people that get a different payout amount.

I am now having an issue to pivot this table so that i can have 2 columns with the payout stage and the average. I need this so that I can use this as a bar chart visualization in metabase.

I have got the stage where i can easily show the averages (see below)

select 
    round(avg(payout_1)::numeric,2) as avg_payout_1,
    round(avg(payout_2)::numeric,2) as avg_payout_2,
    round(avg(payout_3)::numeric,2) as avg_payout_3,
    round(avg(payout_4)::numeric,2) as avg_payout_4
    from payout_table

The result is that i see the averages per payout (yay) however now i want to have the 2 columns, 1 saying the payout type and 2. the average amount payed out

This is my outcome:

|------------------|---------------|---------------|---------------|
|   avg_payout_1   |  avg_payout_2 |  avg_payout_3 |  avg_payout_4 |
|------------------|---------------|---------------|---------------|
|       34.32      |      145.11   |     78.65     |      96.76    |
|------------------|---------------|---------------|---------------|

I would like it to show:

|------------------|---------------|
|      payout      |      avg      |  
|------------------|---------------|
|     payout_1     |      34.32    | 
|------------------|---------------|
|     payout_2     |      145.11   | 
|------------------|---------------|

etc

Answer 1

Use unnest():

select 
    unnest(array[
        'payout_1', 
        'payout_2', 
        'payout_3', 
        'payout_4']) as payout,
    unnest(array[
        round(avg(payout_1)::numeric,2),
        round(avg(payout_2)::numeric,2),
        round(avg(payout_3)::numeric,2),
        round(avg(payout_4)::numeric,2)]) as avg
from payout_table

or jsonb:

select key as payout, value::numeric as avg
from (
    select
        round(avg(payout_1)::numeric,2) as payout_1,
        round(avg(payout_2)::numeric,2) as payout_2,
        round(avg(payout_3)::numeric,2) as payout_3,
        round(avg(payout_4)::numeric,2) as payout_4
    from payout_table
    ) s
cross join jsonb_each(to_jsonb(s))

Db<>fiddle