DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Query returning a matrix

  1. #1
    Join Date
    Apr 2002
    Posts
    41

    Query returning a matrix

    I have a table billcycle which contains the following data

    Billcycle Code

    01 55
    01 56
    01 57
    02 65
    02 66
    02 67

    Can i write a query that gives all the codes of the particular billcycle against the particular billcycle.


    ie
    Billcycle Code

    01 55 56 57
    02 65 66 67

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Do you have standard codes or are these codes are limited?

  3. #3
    Join Date
    Apr 2002
    Posts
    41
    The codes per billcycle may vary ie the data may be

    Billcycle Codes
    01 55
    01 56
    01 57
    01 58
    02 65
    02 66
    03 67

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    There could be a better solution

    Code:
    SQL> SELECT * FROM cycles;
    
            ID CYCLE_TYPE
    ---------- ----------
             1 10
             1 20
             1 30
             1 40
             2 10
             2 20
    
    6 rows selected.
    
    SQL> SELECT Id,  fn_cycletypes(Id) Types FROM cycles
      2  GROUP BY Id;
    
            ID TYPES
    ---------- -------------------------------------------------------
             1 10 20 30 40
             2 10 20
    
    SQL> SELECT DISTINCT Id,  fn_cycletypes(Id) Types FROM cycles;
    
            ID TYPES
    ---------- -------------------------------------------------------
             1 10 20 30 40
             2 10 20
    
    SQL> 
    
    -- Function code
    
    CREATE OR REPLACE FUNCTION fn_cycletypes (nid IN NUMBER)
       RETURN VARCHAR2
    IS 
       sstr   VARCHAR2 (4000) DEFAULT 'X';
    BEGIN
       FOR cur_row IN (SELECT cycle_type
                         FROM cycles
                        WHERE id = nid)
       LOOP
          IF sstr = 'X'
          THEN
             sstr := cur_row.cycle_type;
          ELSE
             sstr := sstr || ' ' || cur_row.cycle_type;
          END IF;
       END LOOP;
    
       RETURN sstr;
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT.put_line (SQLERRM);
          RETURN sstr;
    END fn_cycletypes;
    /
    HTH

    Sameer

Posting Permissions

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


Click Here to Expand Forum to Full Width