My initial problem is that I have a table like this (in reality it is much more complex):
row_name | col_name | val
sm1 | something1 | 10
sm2 | something1 | 15
sm1 | something2 | 12
sm2 | something2 | 13
And I have to produce the following output:
row_name | something1 | something2
sm1 | 10 | 12
sm2 | 15 | 13
I have tried using the CROSSTAB function, but the problem is that the we never know what the row_name and col_name will be (and how many different ones there will be), they are constantly going to be changed, therefore I am unable to define what the output columns should be in CROSSTAB.
What I've been thinking about is storing the values of col_name in an array, and then unnesting the elements of the array into columns.
So I have a function already, which returns the array containing the col_names, let's call it getColNames().
I know that it is possible to unnest the elements into different rows by calling
SELECT UNNEST(getColNames());
Which will result in this:
UNNEST
------------
something1
something2
But would it be possible to unnest it into columns?