Re: matrix transpose in SQL?

Thanks but how extensible do you think your solution is? What if we have a mXn matrix or even mXm matrix, that would result in an explosion of the statement.
I was thinking in terms of joins, or better, numbering each element and so something like:

with temp as

 (select  rownumber() over () as r,
 ((rownumber() over ()-1)*3)+1 as c1,col1,
 ((rownumber() over ()-1)*3)+2 as c2,col2,
 ((rownumber() over ()-1)*3)+3 as c3,col3

                      when x.r=2 then (select col2 from temp where c2=x.r)
                       when x.r=3 then (select col3 from temp where c3=x.r)
            end,
            case when x.r=1 then (select col1 from temp where c1=x.r+3)
                      when r=2 then col2
                       when x.r=3 then (select col3 from temp where
c3=x.r+3)
            end,
            case when x.r=1 then (select col1 from temp where c1=x.r+6)
                      when x.r=2 then (select col2 from temp where c2=x.r+6)
                       when r=3 then col3
            end

Thanks
Aakash

"Mikito Harakiri" <nospam_at_newsranger.com> wrote in message news:T60G6.6951$QV4.596158_at_www.newsranger.com...
> In article <9c9vbp$efu$1_at_stlnews.stl.ibm.com>, Aakash Bordia says...
> >
> >Well I should have rephrased that!
> >Given the data like:
> >col1 col2 col3
> >1 2 3
> >4 5 6
> >7 8 9
> >How will we do it now?
> >We want our table to contain physically:
> >1 4 7
> >2 5 8
> >3 6 9
> >Thanks
> >Aakash
> >
> select c1, c2, c3 from
> (select rownum rn1, c1 from (
> select c1 c1 from (
> select rownum rn, c1 from matrix
> ) where rn = 1
> union
> select c2 c1 from (
> select rownum rn, c2 from matrix
> ) where rn = 1
> union
> select c3 c1 from (
> select rownum rn, c3 from matrix
> ) where rn = 1
> ) ),
> (select rownum rn2, c2 from (
> select c1 c2 from (
> select rownum rn, c1 from matrix
> ) where rn = 2
> union
> select c2 c2 from (
> select rownum rn, c2 from matrix
> ) where rn = 2
> union
> select c3 c2 from (
> select rownum rn, c3 from matrix
> ) where rn = 2
> ) ),
> (select rownum rn3, c3 from (
> select c1 c3 from (
> select rownum rn, c1 from matrix
> ) where rn = 3
> union
> select c2 c3 from (
> select rownum rn, c2 from matrix
> ) where rn = 3
> union
> select c3 c3 from (
> select rownum rn, c3 from matrix
> ) where rn = 3
> ) )
> where rn3 = rn2 and rn1=rn2
>
> Received on Fri Apr 27 2001 - 00:20:32 CEST