Results 1 to 4 of 4

Thread: oracle 8i

  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: oracle 8i

    number f1--- f2 ---- f3 ---- result
    111111 1 --- 0 ---- 0 ------ a
    111111 0 --- 1 ---- 0 ------ b
    111111 0 --- 0 ---- 1 ---- --- c

    the actual records in the table is like in above.

    But I need to consolidate that in one single record

    like
    number f1--- ---f2-------- f3------ result
    111111 1 ----- 1 -------- 1 ---- abc


    what is the best way to reach this.

    thanks in advance
    vijay

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I believe DECODE will do this for you.

    Look it up on the online docs.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    u want this show in a query or do u want to store this in the database?
    If you want a select to to this just try :

    select 'number f1'||f1
    from table;

    If you want to store it like this in ur table, then u have to change the def of the tab, but u don;t want that.

    hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: oracle 8i

    Originally posted by rajuvijay
    number f1--- f2 ---- f3 ---- result
    111111 1 --- 0 ---- 0 ------ a
    111111 0 --- 1 ---- 0 ------ b
    111111 0 --- 0 ---- 1 ---- --- c

    the actual records in the table is like in above.

    But I need to consolidate that in one single record

    like
    number f1--- ---f2-------- f3------ result
    111111 1 ----- 1 -------- 1 ---- abc


    what is the best way to reach this.

    thanks in advance
    vijay
    SQL> create table t (id int, f1 int, f2 int, f3 int, flag varchar2(1));

    Table created.

    SQL> insert into t (id,f1,f2,f3,flag) values (11111, 1, 0, 0, 'a');

    1 row created.

    SQL> insert into t (id,f1,f2,f3,flag) values (11111, 0, 1, 0, 'b');

    1 row created.

    SQL> insert into t (id,f1,f2,f3,flag) values (11111, 0, 0, 1, 'c');

    1 row created.

    SQL> select id, sum(f1),sum(f2), sum(f3),
    2 max (decode(rownum,1,flag,null)) ||
    3 max (decode(rownum,2,flag,null)) ||
    4 max (decode(rownum,3,flag,null))
    5 from t
    6* group by id;


    ID SUM(F1) SUM(F2) SUM(F3) MAX(DECOD
    ---------- ---------- ---------- ---------- ---------
    11111 1 1 1 abc

    You must know the max number of rows to aggregate, unfortunately, for this method to work.

    The max(decode(..)) group by trick is known
    as a "pivot query", that pivots rows to columns - then you use || to concatenate the a,b,c columns into one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •