transpose a matrix in postgresql without using crosstab-CodePudding

In order to share some insights automatically in a slack channel, I need to created a transposed matrix from the input data i have, below you can find the input and output (used excel).

Please note that i cannot use tablefunc extention.

CodePudding user response：

You can use filtered aggregation as shown in this answer

select cat, 
       max(lr_perc) filter (where day = 1) as "1", 
       max(lr_perc) filter (where day = 2) as "2", 
       max(lr_perc) filter (where day = 3) as "3", 
       .... 
from the_table
group by cat;