Unpivoting tables

In a table were there is column for observations (called testName) and the value for the observation (called value). This is so called unpivoted table. We need to store results in this format since list of observations may different across different projects/tables/fields. If we use pivoted table(s) then we will have changing number of columns and a lot of nulls if test is not performed for specififc observation. So unpivoted table is ideal structure to store such data.

The table looks like this:

dimension1 | dimension1 | test_key | test_value 
------------------------------------------------
A1         | B1         | test1    | 47         
A1         | B1         | test3    | 5006
A1         | B2         | test5    | 0,01
A2         | B2         | test1    | 48

Output without filter
dimension1 | dimension1 | test1    | test3   | test5
----------------------------------------------------
A1         | B1         | 47       | 5006    |        
A1         | B2         |          |         | 0,01
A2         | B2         | 48       |         |

Output with filter test_key = test1
dimension1 | dimension1 | test1    
-----------------------------------
A1         | B1         | 47       
A2         | B2         | 48       

For particular dashboards/components we want to filter only specific observations (tests) and present values related to them. So we would like to filter and pivot filtered data.

Ideally we would use something like PIVOT in sql/pandas/pyspark.