Re: matrix transpose in SQL?

Why are you insisting that matrix must be modeled in RDBMS that way? There is a symmetry between rows and columns in matrix, and you loose it in your representation. This is why I cant really take seriously any optimization of the SQL and those SQL statements themself, unless you give a convincing motivation for your model.

"Aakash Bordia" <a_bordia_at_hotmail.com> wrote in message news:9ca6qi$8rg$1_at_stlnews.stl.ibm.com...
> 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
> from invert)
>
> select case when r=1 then col1
> 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
> from temp x
>
> 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 - 07:23:28 CEST