SELECT banner 
FROM   v$version
/

CREATE TABLE subprj
(
  project_id VARCHAR2 (12) NOT NULL,
  project_title VARCHAR2 (20),
  STRUCTURE VARCHAR2 (10),
  project_profile CHAR (1)
)
/

ALTER TABLE SUBPRJ ADD
(
  CONSTRAINT SUBPRJ_PK
  PRIMARY KEY
  (
PROJECT_ID
  )
)
/

INSERT INTO subprj
            (project_id
           , project_title
           , STRUCTURE
           , project_profile
            )
VALUES      ('1'
           , 'project one'
           , 'single'
           , 'P'
            )
/

INSERT INTO subprj
            (project_id
           , project_title
           , STRUCTURE
           , project_profile
            )
VALUES      ('2'
           , 'project two'
           , 'single'
           , 'C'
            )
/

INSERT INTO subprj
            (project_id
           , project_title
           , STRUCTURE
           , project_profile
            )
VALUES      ('3'
           , 'project three'
           , 'multi'
           , 'D'
            )
/

CREATE TABLE sprjcategory
(
  sprjcat_id VARCHAR2 (12) NOT NULL,
  project_id VARCHAR2 (12) NOT NULL,
  CATEGORY VARCHAR2 (255)
)
/

ALTER TABLE sprjcategory ADD
(
  CONSTRAINT sprjcategory_pk
  PRIMARY KEY
  (
    sprjcat_id
  )
)
/

ALTER TABLE sprjcategory ADD
(
  CONSTRAINT sprjcategory_subprj_fk
  FOREIGN KEY
  (
    project_id
  )
  REFERENCES subprj
  (
    project_id
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

INSERT INTO sprjcategory
            (sprjcat_id
           , project_id
           , CATEGORY
            )
VALUES      ('1'
           , '1'
           , 'Application Development'
            )
/

INSERT INTO sprjcategory
            (sprjcat_id
           , project_id
           , CATEGORY
            )
VALUES      ('2'
           , '1'
           , 'Exploratory Research'
            )
/

INSERT INTO sprjcategory
            (sprjcat_id
           , project_id
           , CATEGORY
            )
VALUES      ('3'
           , '2'
           , 'Custom Advisory'
            )
/

INSERT INTO sprjcategory
            (sprjcat_id
           , project_id
           , CATEGORY
            )
VALUES      ('4'
           , '3'
           , 'Exploratory Research'
            )
/

col description format a50
SELECT subprj.project_id
     , subprj.project_title
     , concat_all (concat_expr (sprjcategory.CATEGORY, ',')) description
FROM   subprj
   ,   sprjcategory
WHERE  subprj.project_id = sprjcategory.project_id
GROUP  BY subprj.project_id
        , subprj.project_title
/

DROP TABLE sprjcategory
/

DROP TABLE subprj
/

--CONCAT_EXPR 

CREATE OR REPLACE TYPE "CONCAT_EXPR" AS OBJECT (
   str   VARCHAR2 (4000)
 , del   VARCHAR2 (4000)
 , MAP MEMBER FUNCTION f_map
      RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY concat_expr
AS
   MAP MEMBER FUNCTION f_map
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN str;
   END f_map;
END;
/

--CONCAT_ALL_OT 

CREATE OR REPLACE TYPE "CONCAT_ALL_OT" AS OBJECT (
   str   VARCHAR2 (4000)
 , del   VARCHAR2 (4000)
 , STATIC FUNCTION odciaggregateinitialize (sctx IN OUT concat_all_ot)
      RETURN NUMBER
 , MEMBER FUNCTION odciaggregateiterate (
      SELF   IN OUT   concat_all_ot
    , ctx    IN       concat_expr
   )
      RETURN NUMBER
 , MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       concat_all_ot
    , returnvalue   OUT      VARCHAR2
    , flags         IN       NUMBER
   )
      RETURN NUMBER
 , MEMBER FUNCTION odciaggregatemerge (
      SELF   IN OUT   concat_all_ot
    , ctx2            concat_all_ot
   )
      RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY concat_all_ot
AS
   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT concat_all_ot)
      RETURN NUMBER
   IS
   BEGIN
      sctx    := concat_all_ot (NULL, NULL);
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateiterate (
      SELF   IN OUT   concat_all_ot
    , ctx    IN       concat_expr
   )
      RETURN NUMBER
   IS
   BEGIN
      IF SELF.str IS NOT NULL
      THEN
         SELF.str    := SELF.str || ctx.del;
      END IF;

      SELF.str    := SELF.str || ctx.str;
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       concat_all_ot
    , returnvalue   OUT      VARCHAR2
    , flags         IN       NUMBER
   )
      RETURN NUMBER
   IS
   BEGIN
      returnvalue    := SELF.str;
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregatemerge (
      SELF   IN OUT   concat_all_ot
    , ctx2   IN       concat_all_ot
   )
      RETURN NUMBER
   IS
   BEGIN
      IF SELF.str IS NOT NULL
      THEN
         SELF.str    := SELF.str || SELF.del;
      END IF;

      SELF.str    := SELF.str || ctx2.str;
      RETURN odciconst.success;
   END;
END;
/

CREATE OR REPLACE FUNCTION concat_all (
  ctx IN concat_expr)
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
  AGGREGATE USING concat_all_ot;
/

SQL> @orafaq

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production


Table created.


Table altered.


1 row created.


1 row created.


1 row created.


Table created.


Table altered.


Table altered.


1 row created.


1 row created.


1 row created.


1 row created.


PROJECT_ID   PROJECT_TITLE
------------ --------------------
DESCRIPTION
--------------------------------------------------
1            project one
Application Development,Exploratory Research

2            project two
Custom Advisory

3            project three
Exploratory Research



Table dropped.


Table dropped.

SQL>