Compute trace of matrix in database-CodePudding

I have a table in a Postgres that stores 10x10 matrices, where each row has it's own entry, defined as:

id, matrix_id, row_id, col1, col2, col3...

I'd like to compute the trace (sum of main diagonal) for every matrix identified by its matrix_id, that is, for every matrix_id, I would like to get (col1 where row_id=1) (col2 where row_id=2) (col3 where row_id=3)...

I've tried grouping it by matrix_id but then I cannot use subqueries, something like:

select matrix_id, (select col1 where row_id=1)   (col2 where row_id=2)  
(col3 where row_id=3) ... from matrix group by matrix_id;

but it doesn't work this way.

How could I do that?

CodePudding user response：

So long as they are all 10x10 matrices, use a case statement like so:

select matrix_id,
       sum(
         case row_id
           when 1 then col1
           when 2 then col2
           when 3 then col3
           when 4 then col4
           when 5 then col5
           when 6 then col6
           when 7 then col7
           when 8 then col8
           when 9 then col9
           when 10 then col10
         end
       ) as trace
  from matrix
 group by matrix_id;

Had variable-sized matrices been allowed, you could transpose columns to rows via to_jsonb() and then sum where row_id = <column suffix>.