I'm trying to extract data from a database that stores information in "virtual tables".
This is how the data looks in the database:
| client_id||recordset_id ||column_header ||value |
| 1|| 1000|| Name||John |
| 1||1000 ||City ||Toronto |
|1 || 1000||Province ||ON |
| 1|| 1001|| Name||Michael |
| 1|| 1001|| City|| Vancouver|
| 1|| 1001|| Province|| BC|
|2 || 1002||Name || Rene|
| 2|| 1002|| City||Montreal |
| 2|| 1002||Province ||QC |
In this layout, I can not access the information properly, so I'm trying to create a "classic" table, which should look like this:
|client_id|| recordset_id|| Name||City ||Province |
|1 || 1000||John || Toronto|| ON|
| 1||1001 || Michael||Vancouver || BC|
| 2|| 1002||Rene ||Montreal ||QC |
Both tables contain exactly the same information, client 1 has two names assigned in this list, and client 2 (in green) has only one.
I somehow believe that one of the "Loop" operators might be able to achieve that, but I couldn't figure it out.
Could someone please help me to "transpose" the first table into the second one?
Thank you very much!!