Transform SELECT statement for VARCHARs

From: Detroit Pete <pete_g_at_2xtreme.net>
Date: 1998/07/15
Message-ID: <35AD8535.AD4861F@2xtreme.net>#1/1

I would like to transform a table from a vertical to a horizontal, which has varchar fields instead of numeric fields.

create table MyTable (
  tbl_id number,
  seq_no number,
  someinfo varchar2(10));

insert into MyTable values (100, 1, '100-1');
insert into MyTable values (100, 2, '100-2');
insert into MyTable values (100, 3, '100-3');
insert into MyTable values (101, 1, '101-1');
insert into MyTable values (101, 2, '101-2');
insert into MyTable values (101, 3, '101-3');
insert into MyTable values (102, 1, '102-1');
insert into MyTable values (102, 2, '102-2');
insert into MyTable values (102, 3, '102-3');

/* desired output is

TBL_ID SOMEINFO1 SOMEINFO2 SOMEINFO3

100  100-1   100-2   100-3
101  101-1   101-2   101-3
102  102-1   102-2   102-3

I tried using the standard transforming select statement, */

select distinct tbl_id,

  decode(seq_no, 1, someinfo, null) someinfo1,
  decode(seq_no, 2, someinfo, null) someinfo2,
  decode(seq_no, 3, someinfo, null) someinfo3

from MyTable
group by tbl_id
/

but this produces an error message that the decode is not a GROUP BY expression.
All the examples I have seen use a numeric field so a SUM function can be applied.
But how do transform a table with VARCHARs to produce the above desired result?

TIA Received on Wed Jul 15 1998 - 00:00:00 CDT