Re: matrix transpose in SQL?

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 Thu Apr 26 2001 - 23:08:03 CEST