Results 1 to 10 of 10
  1. #1
    Join Date
    May 2009
    Location
    Czech republic
    Posts
    5

    Unanswered: help with SELECT command on one table

    Hi,

    a have simple data, like this:

    adress.......|..path..|...artikl_no
    ----------- ------ ---------
    1-A-22-1-B...120.....121501
    1-A-48-1-A...120.....121501
    1-A-56-2-C...121.....121501

    I need this data:

    artikl_no....|...adresses......................... .......... |.....paths
    ----------- ----------------------------------- ------------
    121501.......1-A-22-1-B,1-A-48-1-A,1-A-56-2-C......120,120,121

    I don't know number of items "artikl" in advance; range number of items between 1 and 5.

    What is the name of the technics?

    thank's
    resko

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    This can be done nativity in 11G or with a function in 9I and 10G. Which version do you have?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2009
    Location
    Czech republic
    Posts
    5
    I have 10g version.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It is very doable. See the following link.

    http://asktom.oracle.com/pls/asktom/...:2196162600402
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2009
    Location
    Czech republic
    Posts
    5
    Thanks, I need find "concatenate" . I found ORACLE-BASE - String Aggregation Techniques - function wm_concat()

  6. #6
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    10g and above also has wm_concat() aggregate function.

    SELECT artikl_no, wm_concat(address) AS addresses,
    wm_concat(path) AS paths
    FROM articles
    GROUP BY artikl_no

  7. #7
    Join Date
    May 2009
    Location
    Czech republic
    Posts
    5
    thanks artacus72 - it is simple :-)

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I thought that that wasn't available until 11. Live and learn. Thanks artacus72
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Dec 2009
    Posts
    18
    Hi everyone,
    using this cursor fine but I am not getting data into my dw_time table and dw_spend_fact
    Any help please !!!

    SQL> DECLARE
    2 cursor c1 is select
    3 a.transaction_id,a.supplier_id,a.CATEGORY_ID,a.gro ss,a.order_date,c.cardholder_id, d.department_id
    4 FROM transaction_cleansig1 a,card_cleansig b,cardholder_cleansig c,department_cleansig d
    5 WHERE a.card_id=b.card_id
    6 AND
    7 b.cardholder_id=c.cardholder_id
    8 AND
    9 c.department_id=d.department_id
    10 order by a.transaction_id;
    11
    12 icount integer := 0;
    13 spid varchar2(20);
    14 ctid varchar2(20);
    15 chid varchar2(20);
    16 depid varchar2(20);
    17 month integer;
    18 year integer;
    19
    20 BEGIN
    21 for rec in c1 loop
    22
    23 icount := icount + 1;
    24
    25 month := to_char(rec.order_date, 'MM');
    26 year := to_char(rec.order_date, 'YYYY');
    27 insert into dw_time values (icount,rec.order_date,year,month);
    28
    29 select supplier_id into spid from dw_supplier where supplier_id=rec.supplier_id;
    30 select category_id into ctid from dw_category where CATEGORY_ID=rec.CATEGORY_ID;
    31 select cardholder_id into chid from dw_cardholder where cardholder_id=rec.cardholder_id;
    32 select department_id into chid from dw_department where department_id=rec.department_id;
    33 insert into dw_spend_fact values (icount, depid, spid, ctid, chid, rec.gross);
    34 end loop;
    35 END;
    36 /

    PL/SQL procedure successfully completed.

    SQL> select *from dw_time;

    no rows selected

    SQL> select *from dw_spend_fact;

    no rows selected

    I am not getting data into my dw_time table and dw_spend_fact
    Any help please !!!
    Last edited by rolmau; 12-31-09 at 10:39.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Please start a new thread with a new posting.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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