Data Transposition Problem Still Persists

From: <puneet.bansal_at_wipro.com>
Date: 10 Jun 2005 09:23:58 -0700
Message-ID: <1118420638.843462.136830@z14g2000cwz.googlegroups.com>

Hi,

Yesterday I had posted a problem which went like this - 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

Rene suggested a solution as below -

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;

This works fine for X and Y, but what happens when col1 has more than two values values which are not known in advance? Any suggestions on how to handle the full outer join in such a case?

Thanks.
Puneet Received on Fri Jun 10 2005 - 11:23:58 CDT