0

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?

  • See [Turning arbitrarily many rows into columns in PostgreSQL](https://stackoverflow.com/q/41542663/1995738) – klin Sep 30 '21 at 11:49

0 Answers0