1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help on an Oracle SQL Query

Discussion in 'SQL PL/SQL' started by SARAHK, Oct 27, 2010.

  1. SARAHK

    SARAHK Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    HI,

    I have a table and for each unique parent key there are between 1 and 5 rows. As per example below with 3 rows. I need to get this data in 1 row with the unique fields following as per second table below.

    Is there any way this can be done by sql in Oracle?


    PARENT
    KEY LA HA AR
    1 0 15000 98
    1 15000 30000 99
    1 30000 99999999 100


    PARENT
    KEY LA1 HA1 AR1 LA2 HA2 AR2 LA3 HA3 AR3
    1 0 15000 98 15000 30000 99 30000 99999999 100

    Any help would be appreciated!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The answer depends upon which release of Oracle is in use. Post the version of Oracle you're using.
     
  3. SARAHK

    SARAHK Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Its Oracle XE - SQL Developer
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You'll need to learn how to use the DECODE function; here's an example:

    Code (SQL):
     
    SQL> --
    SQL> -- Using decode
    SQL> --
    SQL>
    SQL> SELECT DECODE(asst_storemgr1, 0,
      2         DECODE(asst_storemgr2, 0,
      3         DECODE(asst_storemgr3, 0, 0, asst_storemgr3),
      4         asst_storemgr2), asst_storemgr1) ASST_MANAGER,
      5         DECODE(store_mgr1,0, DECODE(store_mgr2,0, 0, store_mgr2), store_mgr1) STORE_MANAGER,
      6         REGION_MGR,
      7         REGION_DIR
      8  FROM stores;
    ASST_MANAGER STORE_MANAGER REGION_MGR REGION_DIR
    ------------ ------------- ---------- ----------
             401           301        200        100
             404           301        200        100
             405           305        200        100
             407           306        250        100
    SQL>

     
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, SYS_CONNECT_BY_PATH could work provided the separator value is not included in the concatenated string of values:

    Code (SQL):
     
    SQL> SELECT KEY,
    2 MAX(sys_connect_by_path(lx, ' ' )) lahara
    3 FROM (SELECT KEY, la||'-'||ha||'-'||ar lx,
    4 ROW_NUMBER() OVER (partition BY KEY ORDER BY la) rn
    5 FROM parent
    6 )
    7 START WITH rn = 1
    8 CONNECT BY prior rn = rn-1 AND prior KEY = KEY
    9 GROUP BY KEY
    10 ORDER BY KEY
    11 /
    KEY LAHARA
    ---------- --------------------------------------------------------------------------------
    1 0-15000-98 15000-30000-99 30000-9999999-100
    SQL>
     
     
    However I'm not convinced this is the desired output according to the original post.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    One can also use the WM_CONCAT function:

    Code (SQL):
     
    SQL> --
    SQL> -- Using wm_concat
    SQL> --
    SQL>
    SQL> SELECT KEY, wm_concat(lx) lahara
      2  FROM (SELECT KEY, la||' '||ha||' '||ar lx
      3        FROM parent
      4        )
      5  GROUP BY KEY
      6  /
           KEY LAHARA
    ---------- --------------------------------------------------------------------------------
             1 0 15000 98,15000 30000 99,30000 9999999 100
    SQL>

     
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Or xmlagg:

    Code (SQL):
     
    SQL> SELECT KEY
      2       , RTRIM
      3         ( xmlagg (xmlelement (c, lx || ',') ORDER BY lx).EXTRACT ('//text()')
      4         , ',' ) AS lahara
      5  FROM (SELECT KEY, la||' '||ha||' '||ar lx
      6        FROM parent
      7        )
      8  GROUP BY KEY;
           KEY LAHARA
    ---------- --------------------------------------------------------------------------------
             1 0 15000 98,15000 30000 99,30000 9999999 100
    SQL>

     
     
  9. SARAHK

    SARAHK Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Will these methods work if there is more than 1 key?

    I have 8 keys, and depending on the values of these i will have a few seperate values for LOAP, HIAP, RATE (max 5)

    Output I am hoping to achieve is:

    KEY1 .......... KEY8 LOAP1 HIAP1 RATE1 LOAP2 HIAP2 RATE2 LOAP3 HIAP3 RATE3



    I need all the fields above in seperate columns

    LOAP1 always equals 0, HIAP1 = LOAP2 AND HIAP2 = LOAP3. I dont need the LOAP fields in the output anything i was trying previously it seemed easier to have them in it.

    I could get this query to work in access but the tables were too big so i had to move to Oracle XE
     
  10. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    SARAHK, why don't you try... :)
     
  11. SARAHK

    SARAHK Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I have tried this for the last 2 days. This method does not work for more than 1 key and even with 1 key it will not seperate the columns correctly.

    select
    ALLOCATION_ID, LOFREQ, HIFREQ, LOAGE, HIAGE, LOTERM, HITERM, LODURATION, HIDURATION,
    SYS_CONNECT_BY_PATH (LOAP, HIAP, ALLOCATION_RATE '|') name_list
    from
    (
    select
    LOAP, HIAP, ALLOCATION_RATE
    PARENT,
    count(*) OVER ( partition by ALLOCATION_ID, LOFREQ, HIFREQ, LOAGE, HIAGE, LOTERM, HITERM, LODURATION, HIDURATION) cnt,
    ROW_NUMBER () OVER ( partition by ALLOCATION_ID, LOFREQ, HIFREQ, LOAGE, HIAGE, LOTERM, HITERM, LODURATION, HIDURATION order by LOAP) seq
    from
    MISPRC.ALLOC_RATE)
    where
    seq=cnt
    start with
    seq=1
    connect by prior
    seq+1=seq
    and prior
    ALLOCATION_ID = ALLOCATION_ID AND
    LOFREQ = LOFREQ AND
    HIFREQ = HIFREQ AND
    LOAGE = LOAGE AND
    HIAGE = HIAGE AND
    LOTERM = LOTERM AND
    HITERM = HITERM AND
    LODURATION = LODURATION AND
    HIDURATION = HIDURATION
    ;
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you must have data in separate columns then the only way to do that with XE is using the decode example I posted.