Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Balakumar.

Asked: September 07, 2015 - 7:57 am UTC

Last updated: September 14, 2015 - 4:13 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

Hi Tom,

I'am having table and its corresponding data like below

CREATE TABLE TRANSPOSE (A NUMBER, B NUMBER, C NUMBER);

DATA
A B C
1 4 7
2 5 8
3 6 9

I need output as below (Transpose data).
A B C
1 2 3
4 5 6
7 8 9

Kindly provide simple query to achive this example?

Thanks,
Bala

and Chris said...

First, convert the table from a matrix into a 2d array. So each row gives the value stored in position (x,y) in the matrix.

You can do this by joining the table to a three row table (I've used connect by level row generator for this):

CHRIS>select *
  2  from   transpose, (select rownum y from dual connect by level <= 3) r;

         A          B          C          Y
---------- ---------- ---------- ----------
         1          4          7          1
         2          5          8          1
         3          6          9          1
         1          4          7          2
         2          5          8          2
         3          6          9          2
         1          4          7          3
         2          5          8          3
         3          6          9          3


You can get the X values by taking the modulus of the rownum. The Y values from the generated table state which column you should fetch the value from. Use case/decode to get this:

CHRIS>select mod(rownum-1, 3)+1 x, y, decode(y, 1, a, 2, b, 3, c) val
  2  from   transpose, (select rownum y from dual connect by level <= 3) r;

         X          Y        VAL
---------- ---------- ----------
         1          1          1
         2          1          2
         3          1          3
         1          2          4
         2          2          5
         3          2          6
         1          3          7
         2          3          8
         3          3          9


To transpose the rows we need (x, y) => (y, x). Do this by grouping by y and using another decode to get the appropriate value based on X.

Put it all together and you get this:

CHRIS>select max(decode(x, 1, val)) a,
  2         max(decode(x, 2, val)) b,
  3         max(decode(x, 3, val)) c
  4  from (
  5  select mod(rownum-1, 3)+1 x, y,
  6         decode(y, 1, a, 2, b, 3, c) val
  7  from   transpose, (select rownum y from dual connect by level <= 3) r
  8  )
  9  group  by y
 10  order  by y;

         A          B          C
---------- ---------- ----------
         1          2          3
         4          5          6
         7          8          9



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Little bit complex

Balakumar Subramanian, September 07, 2015 - 11:09 am UTC

Thanks Tom for quick response. Any other way to achieve the same result?. Because its very difficult to understand. Even though I'll refer this and am expecting little simple.
Thanks,
Bala
Chris Saxon
September 07, 2015 - 3:04 pm UTC

There are other ways you could write the query. Here's a couple.

You said you're on 10g, so here's an example using the model clause:

CHRIS>select a1, b1, c1
  2  from   (
  3    select a, b, c, 1 x, 1 y
  4    from   transpose
  5  )
  6  model dimension by ( rownum-1 x , rownum-1 y )
  7        measures ( 0 a1, 0 b1, 0 c1, a , b, c )
  8        rules upsert all iterate (3)
  9        (
 10          a1[iteration_number, iteration_number] =
 11                      case when cv(y) = 0 then a[0, 0]
 12                           when cv(y) = 1 then b[0, 0]
 13                           else c[0, 0]
 14                      end,
 15          b1[iteration_number, iteration_number] =
 16                      case when cv(y) = 0 then a[1, 1]
 17                           when cv(y) = 1 then b[1, 1]
 18                           else c[1, 1]
 19                      end,
 20          c1[iteration_number, iteration_number] =
 21                      case when cv(y) = 0 then a[2, 2]
 22                           when cv(y) = 1 then b[2, 2]
 23                           else c[2, 2]
 24                      end
 25        )
 26  order  by x, y;

        A1         B1         C1
---------- ---------- ----------
         1          2          3
         4          5          6
         7          8          9


Or if you're on 11g or higher you can unpivot+pivot:

CHRIS>select a, b, c from (
  2    select *
  3    from   (select rownum r, a, b, c from transpose)
  4    unpivot (
  5      val for col in (A, B, C)
  6    )
  7  )
  8  pivot (sum(val) for r in ('1' a, '2' b, '3' c));

         A          B          C
---------- ---------- ----------
         1          2          3
         4          5          6
         7          8          9

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.