I work with QGIS and PostgreSQL with PostGIS. I need help with dynamic queries for PostgreSQL.

Information is structured in tables that contain votes for parties, and other types of information like geographic area or election date.

I need to work with “tidy” data in plotly for pie charts. The desired end table must have one row per observation.

So for example for the given table “Election Results”:

Values for columns to be treated like parties, are stored in table “Parties”:

I need to separate one observation of voting results per row as follows (“Ending Table”):

The query should work with any number of parties stored in the “Parties” table. The “parties” table could include some rows not present in the election results table, but all parties in the election results table will exist in the “parties” table.

I understand it should be done iterating over the columns in the “Election results” table. If the name of a column matches the value of a row in “party table”, then we “untidy” data as in the “ending table”.

CodePudding user response：

You can "iterate" over the columns using Postgres's JSON functions:

Something like this:

select er."Country", 
       pv.*
from election_results er
  cross join jsonb_each_text(to_jsonb(er) - 'Country') as pv(party, votes)
  join parties p on p.party = pv.party
;

Online example