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

user_15969876 0
Viewed 201

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
unpivot postgresql sql python
1 Answers

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
  1. 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

user_77724830
Related