Re: Interesting data transposition problem

On 2005-06-09, puneet.bansal_at_wipro.com <puneet.bansal_at_wipro.com> wrote:
> Hi,
>
> I am handling a complex query and ultimately the problem boils down to
> this.
> I have the data in the format
>
> Col1 Col2
> X a
> X b
> Y c
> Y d
> Y e
>
> but I need this data in the following format (note that the column
> names have changed to the data)
>
> X Y
> a c
> b d
> e
>
> I have managed to get
> X Y
> a
> b
> c
> d
> e
>
> but can't get what I want. I am using the following query -
>
>
> select decode(col1,'X',col2) as x,
> decode(col1,'Y',col2) as y
> from
> (
> select 'X' as col1, 'a' as col2 from dual
> union all
> select 'X' as col1, 'b' as col2 from dual
> union all
> select 'Y' as col1, 'c' as col2 from dual
> union all
> select 'Y' as col1, 'd' as col2 from dual
> union all
> select 'Y' as col1, 'e' as col2 from dual
> )
>
> Can anyone please help?

create table ttt (
  col1 varchar2(10),
  col2 varchar2(10)
);

insert into ttt values ('X', 'a');
insert into ttt values ('X', 'b');
insert into ttt values ('Y', 'c');
insert into ttt values ('Y', 'd');
insert into ttt values ('Y', 'e');

select x.x, y.y from (
  select
    rownum r, col2 x
  from
    ttt
  where
    col1 = 'X'
) x natural full join (
  select
    rownum r, col2 y
  from
    ttt
  where
    col1 = 'Y'
) y;

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/