Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69

    Unanswered: Merging row data...

    I have data like this after a query:

    C D Training
    1 Desc1 N/A
    2 Desc2 Training Required
    3 Desc3 Bioburden Test

    I would like to get:
    Training Required, Bioburden Test

    I'm working on Oracle 8i. I've read many articles in the web about procedures
    but none seems to work.

    Any idea?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation


    Yes, the best idea is to try searching the term 'pivot' or 'crosstab' or 'rows to columns'.

    This question has been asked and answered hundreds if not thousands of times -- do some research!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Do a search for stragg on asktom.

    Alan

  4. #4
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Stragg looks great! Sadly is not available in Oracle 8i. That's basically the reason of the post. All the answers are with fucntions that doesn't work on 8i...

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As Oracle corp. doesn't support Oracle 8i any more (for quite a while now), could you upgrade your database to a more recent version?

    (Not that this is obligatory; my company still has some 7.3 instances, most of them are 8.1.7's, while only a few - as far as I know - are 9i. I'm not sure if there's any 10g in production.)

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I have stragg working on oracle 8. Read the ENTIRE article below, it shows how to do it in 8.1.7

    http://asktom.oracle.com/pls/ask/f?p...:2200571416651
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Thanks for the answer, but sadly as with other answers I get this error:

    [Error Code: 6575, SQL State: 65000] ORA-06575: Package or function PRODUCT is in an invalid state
    select product('training',training,'open','review_respons e') from (select distinct training from review_response where c=1162567280);

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Paste the contents of your product function.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    From:

    Quote Originally Posted by beilstwh
    I have stragg working on oracle 8. Read the ENTIRE article below, it shows how to do it in 8.1.7

    http://asktom.oracle.com/pls/ask/f?p...:2200571416651

    create or replace
    function product( p_key_name in varchar2,
    p_key_val in varchar2,
    p_other_col_name in varchar2,
    p_tname in varchar2 )
    return number
    as
    type rc is ref cursor;
    l_num number;
    l_val number;
    l_cur rc;
    begin

    open l_cur for 'select '||p_other_col_name||'
    from '|| p_tname || '
    where '|| p_key_name || ' = :x
    and '||p_other_col_name|| ' is not null'
    using p_key_val;

    loop
    fetch l_cur into l_val;
    exit when l_cur%notfound;
    l_num := nvl(l_num,1) * l_val;

    end loop;
    close l_cur;

    return l_num;
    end;
    /

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I am sorry, I pointed you to the wrong article. I will find the correct one and give you the reference.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69
    Thnaks for the correction but I have the same error...

    [CREATE - 0 row(s), 0.094 secs] Command processed but no rows were affected
    create or replace
    function get_transposed( p_a in varchar2 )
    return varchar2
    is
    l_str varchar2(2000) default null;
    l_sep varchar2(1) default null;
    begin
    for x in ( select b from t where a = p_a ) loop
    l_str := l_str || l_sep || x.b;
    l_sep := '-';
    end loop;
    return l_str;
    end;
    /
    ;
    ... 1 statement(s) executed, 0 row(s) affected, database exec time 0.094 sec [0 successful, 1 warnings, 0 errors]


    The error states that the procedure is an illegal state...

    [SELECT - 0 row(s), 0.016 secs] [Error Code: 6575, SQL State: 65000] ORA-06575: Package or function GET_TRANSPOSED is in an invalid state
    select c, get_transposed( c ) t from review_response group by c;
    ... 1 statement(s) executed, 0 row(s) affected, database exec time 0.016 sec [0 successful, 0 warnings, 1 errors]

  13. #13
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Did you actually noticed that the function is selecting column named B from a table named T ?

  14. #14
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    And what?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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