select multiple rows into one row
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: select multiple rows into one row

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    select multiple rows into one row

    i want to query from two tables and return one row. Problem is there could be multiple rows in the 2nd table.

    Table tabs
    id name
    ----------------
    1 myTab
    2 yourTab

    Table tabsaccess
    tab_id user_id group_id
    ------------------------
    1 1700 null
    1 8 null
    1 null 5000
    1 null 5001

    i want to query for a tab id and return the id, name, a list of user_ids and a list of group ids in one row.

    Return
    id name user_id group_id
    --------------------------
    1 myTab [1700, 8] [5000, 5001]

    Is it possible?

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    It's a variation on a "pivot" table:
    http://asktom.oracle.com/pls/ask/f?p...:766825833740,

  3. #3
    Join Date
    Dec 2003
    Posts
    90

    proc

    ok here is my procedure based on the site you posted. Type ONEDNUMBERARRAY is type ONDNUMBERARRAY as table of number.

    PROCEDURE GetTabAccess(ATabID IN NUMBER,
    DATA_CURSOR OUT Types.CursorType)
    IS
    BEGIN

    OPEN DATA_CURSOR FOR
    select id,
    cast ( multiset( select user_id
    from caw_tabsaccess
    where caw_tabsaccess.tab_id = caw_tabs.id and caw_tabsaccess.user_id IS NOT null
    group by user_id ) as ONEDNUMBERARRAY ) USER_ID
    from caw_tabs;
    END GetTabAccess;

    which compiles and seems to work but the script output is

    ID USER_ID
    ------------- -----------
    1
    2
    6
    7
    8
    9
    10
    11
    12
    13
    19
    20
    21
    91
    92
    100
    101
    102
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    28 rows selected


    so how do i get my data out of the in USER_ID column?
  4. #4
    Join Date
    Dec 2003
    Posts
    90
    sorry when i posted nothing showed up, in the user_id column all i get is ""
  5. #5
    Join Date
    Jan 2004
    Posts
    162
    Perhaps a user-defined string aggregate is more helpful here.
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production
    
    SQL> CREATE TABLE tabs (
      2    id NUMBER,
      3    name VARCHAR2 (10));
    
    Table created.
    
    SQL> INSERT INTO tabs VALUES (1, 'myTab');
    
    1 row created.
    
    SQL> INSERT INTO tabs VALUES (2, 'yourTab');
    
    1 row created.
    
    SQL> CREATE TABLE tabsaccess (
      2    tab_id NUMBER,
      3    user_id NUMBER,
      4    group_id NUMBER);
    
    Table created.
    
    SQL> INSERT INTO tabsaccess VALUES (1, 1700, NULL);
    
    1 row created.
    
    SQL> INSERT INTO tabsaccess VALUES (1, 8, NULL);
    
    1 row created.
    
    SQL> INSERT INTO tabsaccess VALUES (1, NULL, 5000);
    
    1 row created.
    
    SQL> INSERT INTO tabsaccess VALUES (1, NULL, 5001);
    
    1 row created.
    
    SQL> CREATE OR REPLACE TYPE string_ot AS OBJECT (
      2    total VARCHAR2 (4000),
      3    
      4    STATIC FUNCTION odciaggregateinitialize (
      5      sctx IN OUT string_ot)
      6      RETURN NUMBER,
      7      
      8    MEMBER FUNCTION odciaggregateiterate (
      9      SELF IN OUT string_ot,
     10      VALUE IN VARCHAR2)
     11      RETURN NUMBER,
     12      
     13    MEMBER FUNCTION odciaggregateterminate (
     14      SELF IN string_ot,
     15      returnvalue OUT VARCHAR2,
     16      flags IN NUMBER)
     17      RETURN NUMBER,
     18      
     19    MEMBER FUNCTION odciaggregatemerge (
     20      SELF IN OUT string_ot,
     21      ctx2 IN string_ot)
     22      RETURN NUMBER
     23  );
     24  /
    
    Type created.
    
    SQL> CREATE OR REPLACE TYPE BODY string_ot
      2  AS
      3    STATIC FUNCTION odciaggregateinitialize (
      4      sctx IN OUT string_ot)
      5      RETURN NUMBER
      6    IS
      7    BEGIN
      8      sctx := string_ot (NULL);
      9      RETURN odciconst.success;
     10    END;
     11    
     12    MEMBER FUNCTION odciaggregateiterate (
     13      SELF IN OUT string_ot,
     14      VALUE IN VARCHAR2)
     15      RETURN NUMBER
     16    IS
     17    BEGIN
     18      SELF.total := SELF.total || ', ' || VALUE;
     19      RETURN odciconst.success;
     20    END;
     21    
     22    MEMBER FUNCTION odciaggregateterminate (
     23      SELF IN string_ot,
     24      returnvalue OUT VARCHAR2,
     25      flags IN NUMBER)
     26      RETURN NUMBER
     27    IS
     28    BEGIN
     29      returnvalue := SUBSTR (SELF.total, 3);
     30      RETURN odciconst.success;
     31    END;
     32    
     33    MEMBER FUNCTION odciaggregatemerge (
     34      SELF IN OUT string_ot,
     35      ctx2 IN string_ot)
     36      RETURN NUMBER
     37    IS
     38    BEGIN
     39      SELF.total := SELF.total || ctx2.total;
     40      RETURN odciconst.success;
     41    END;
     42  END;
     43  /
    
    Type body created.
    
    SQL> CREATE OR REPLACE FUNCTION string_agg (
      2    input IN VARCHAR2)
      3    RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
      4    AGGREGATE USING string_ot;
      5  /
    
    Function created.
    
    SQL> COLUMN user_id FORMAT A20
    SQL> COLUMN group_id FORMAT A20
    SQL> SELECT id, name, 
      2        (SELECT '[' || string_agg (user_id) || ']'
      3         FROM   tabsaccess
      4         WHERE  user_id IS NOT NULL
      5         AND    tab_id = id) user_id,
      6        (SELECT '[' || string_agg (group_id) || ']'
      7         FROM   tabsaccess
      8         WHERE  group_id IS NOT NULL
      9         AND    tab_id = id) group_id
     10  FROM   tabs
     11  WHERE  id = 1;
    
            ID NAME       USER_ID              GROUP_ID
    ---------- ---------- -------------------- --------------------
             1 myTab      [1700, 8]            [5000, 5001]
    
    SQL>

  6. 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