How to get a list of fields and distinct values from a table

Question

I need to unpivot columns to rows and then have the distinct values alongside. Ideally using postgresql but could use python if solution is easier. Also needs to be dynamic as the fields will vary each time the transformation is run. I've trawled through the search but struggling to find anything that resembles this.

Source data table:

option1	option2	option3
1	A	X
1	B	Y
2	A	X
3	B	Y
3	A	X

Target table:

fieldname	option
option1	1
option1	2
option1	3
option2	A
option2	B
option3	X
option3	Y

Answer 1

There are 2 ways to do that

1. Using UNNEST

select distinct 
unnest(array['option1','option2','option3']),
unnest(array[option1,option2,option3])
from test
order by 1,2

2. Using Cross Join Lateral

select distinct t2.*
from test t1
  cross join lateral (
     values 
       ('option1',t1.option1 ),
       ('option2',t1.option2),
       ('option3',t1.option3)
       
  ) as t2(option, value)
order by 1,2

DEMO