Re: Transposing a table !

I joined this thread late so I apologize if this solution has alreaby been given. But you can try:

SELECT prod_no,'1',val_1 FROM tabla
UNION
SELECT prod_no,'2',val_2 FROM tabla
UNION
SELECT prod_no,'3',val_3 FROM tabla
etc.....

HTH,
Brian

dlane_at_cix.compulink.co.uk wrote:
>
> 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)
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================