Re: Transform SELECT statement for VARCHARs

From: Lee H. C. <leehc_at_speednet.net>
Date: 1998/07/17
Message-ID: <6ono61$df6@sn2.speednet.net>#1/1

You may try this:

select tbl_id,

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

from MyTable
group by tbl_id

Detroit Pete ¼¶¼g©ó¤å³¹ <35AD8535.AD4861F_at_2xtreme.net>...
>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 Fri Jul 17 1998 - 00:00:00 CDT