Re: Transposing a table !

From: <dlane_at_cix.compulink.co.uk>
Date: 2000/05/20
Message-ID: <8g5q21$dap$1@plutonium.compulink.co.uk>#1/1

In article <391914E1.70286CAC_at_wanadoo.fr>, elnikou_at_wanadoo.fr (Yannick) wrote:

> Here is the problem :
>
> I have the following table tbla :
>
> Prod_no val_1 val_2 val_3 ....
> -------------------------------------
> P1 5 15 10
> P2 8 6 4
> .....
>
>
> and I would like to get following result :
>
> Prod_no val_no value
> --------------------------------
> P1 1 5
> P1 2 15
> P1 3 10
> P2 1 8
> P2 2 6
> P2 3 4
> ....
>
> Can anybody help me to find a nice solution avoinding something like
> select prod_no,1,val_1 from tbla union select prod_no,2,val_2 from tbla
> .....
>
> Thanks in advance

How about:

create table dummy (val_no number(2));

insert into dummy values (1);
insert into dummy values (2);
insert into dummy values (3);

-- etc for all possible values ... then .... select tbla.prod_no,dummy.val_no,

    decode(dummy.val_no,1,val_1,2,val_2,3,val_3,to_number(null)) value     from tbla,dummy;

This might be slow!

Dave Lane (dlane_at_pt.lu) Received on Sat May 20 2000 - 00:00:00 CDT