Re: Transform SELECT statement for VARCHARs

Try this,

SELECT T1.TBL_ID, T1.SOMEINFO SOMEINFO1, T2.SOMEINFO SOMEINFO2, T3.SOMEINFO SOMEINFO3
FROM MYTABLE T1, MYTABLE T2, MYTABLE T3
WHERE
T1.SEQ_NO = 1
AND
(T2.TBL_ID = T1.TBL_ID AND T2.SEQ_NO = 2) AND
(T3.TBL_ID = T1.TBL_ID AND T3.SEQ_NO = 3) Detroit Pete <pete_g_at_2xtreme.net> wrote in article <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 Thu Jul 16 1998 - 00:00:00 CDT