Oracle SQL – Single Query to Transpose a Matrix

oracle

I am facing a problem in writing a query for this question.Consider two random 3×3 (n=3) matrices A and B,having the following schema:

A(i,j,val) and B(i,j,val) where

i int:Row Index

j int:Coloumn Index

val int:Cell Value

Write a single query to get the matrix transpose(A) in the same format as A ie.,output tuples should be of format (i,j,val) where i is row, j is coloumn and val is cell value.

Best Answer

All you need to do is swap the indexes. Original:

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
)
select
  *
from
  A
order by
  1, 2
;

  I   J VAL
--- --- ---
  1   1   1
  1   2   2
  1   3   3
  2   1   4
  2   2   5
  2   3   6
  3   1   7
  3   2   8
  3   3   9

Transposed:

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
)
select
  j as i,
  i as j,
  val
from
  A
order by
  1, 2
;

  I   J VAL
--- --- ---
  1   1   1
  1   2   4
  1   3   7
  2   1   2
  2   2   5
  2   3   8
  3   1   3
  3   2   6
  3   3   9
Related Question