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>




Reply With Quote