title: Is it possible to have multiple listagg statements referencing more than one table in one query?
body: <p>I have three LISTAGG statements that look like this:</p>
  
  <pre><code>LISTAGG (ga.FULL_APPL_NUM, ',') WITHIN GROUP (ORDER BY   ga.FULL_APPL_NUM) AS "FullApplNums",
  LISTAGG (ga.APPL_ID, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "ApplIds",
  LISTAGG (ga.PROJECT_TITLE, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "ProjectTitles",
  </code></pre>
  
  <p>The results look like this:</p>
  
  <p><strong>FullApplNums</strong><br>
  2r01HL02858573-23A1,5R01HL02857324,...<br>
  <strong>ApplIds</strong><br>
  7219924,6718409,... <br>
  <strong>ProjectTitles</strong><br>
  ProjectTitle1, ProjectTitle2,...<br></p>
  
  <p>These columns all reference the same record from one table in a join to about 4 tables but they are all coming from the same table - ga.</p>
  
  <p>I need another list agg to fill in one more column but it comes from another table aliased as pp.
  Is it possible to LISTAGG again referencing another table and how would I sync up the values since the comma delimated lists are used like index arrays.
  So I can build a record from ga["FULL_APPL_NUM"][0], ga["APPL_ID"][1], ga["PROJECT_TITLE"][0].</p>
  
  <p>Now I need ga["IsContact"][0] but it would come from pp table not ga.</p>
  
  <p>Or is it possible to do a sub select query like:
  SELECT * FROM pp where appl_id in (ApplIds) -- where ApplIds is the result of a list agg.</p>
  
  <p>Here is the whole query so far:</p>
  
  <pre><code> PROCEDURE GetPrincipalInvestigators
        (
   loginId          IN VARCHAR2 := NULL,
   portfolioId      IN NUMBER := NULL,
   portfolioType    IN VARCHAR := NULL,
   ic               IN VARCHAR2 := NULL,
   startRow         IN INT := NULL,
   endRow           IN INT := NULL,
   sortField        IN VARCHAR2 := NULL,
   sortDirection    IN VARCHAR2 := NULL,
   PrincipalInvestigators_CUR IN OUT SYS_REFCURSOR
    )
        IS
            v_APPL_ID               NUMBER(10,0) := 0;  
    v_ADMIN_PHS_ORG_CODE    VARCHAR2(2 BYTE) := '  ';
    v_SERIAL_NUM            NUMBER(6,0) := 0;
    v_Proj_Appl_Rec         Proj_Appl_Rec;        
    v_Proj_Appl_Tab         Proj_Appl_Tab := Proj_Appl_Tab(); 
    v_Proj_Appl_Cur         SYS_REFCURSOR;
    v_sortField             VARCHAR2(50 BYTE) := NULL;
    v_sortDirection         VARCHAR2(4 BYTE)  := NULL;
    v_cnt                   NUMBER := 0;
    v_orderBy               VARCHAR2(200 BYTE) := ' ORDER BY ';
    v_sql                   CLOB := ' ';  
  
    BEGIN  
        IF sortField IS NULL THEN
          v_sortField := 'LAST_NAME';
        ELSE
          v_sortField := sortField;
        END IF;
  
        IF sortDirection IS NULL THEN
          v_sortDirection := 'DESC';
        ELSE
          v_sortDirection := sortDirection;
        END IF;
  
        v_orderBy := v_orderBy || v_sortField || ' ' || v_sortDirection; 
        --DBMS_OUTPUT.PUT_LINE(v_orderBy); 
  
        IREPORT_PORTFOLIOS.GetPortfolioAppsAndProjects
        (
          loginId =&gt; loginId,
          portfolioId =&gt; portfolioId,
          portfolioType =&gt; portfolioType,
          ic =&gt; ic,
          AppIds_CUR =&gt; v_Proj_Appl_Cur
        );
  
        LOOP
            FETCH v_Proj_Appl_Cur         
            INTO v_APPL_ID, v_ADMIN_PHS_ORG_CODE, v_SERIAL_NUM;        
            EXIT WHEN v_Proj_Appl_Cur%NOTFOUND;        
            v_Proj_Appl_Tab.extend;
            v_cnt := v_cnt + 1;
            v_Proj_Appl_Tab(v_cnt) := Proj_Appl_Rec(v_APPL_ID, v_ADMIN_PHS_ORG_CODE, v_SERIAL_NUM);        
        END LOOP;
        CLOSE v_Proj_Appl_Cur; 
  
        OPEN PrincipalInvestigators_CUR FOR
            WITH projects_CTE
            AS 
            (
               SELECT DISTINCT
                      pa.APPL_ID,
                      pa.ADMIN_PHS_ORG_CODE,
                      pa.SERIAL_NUM
                 FROM TABLE(v_proj_appl_tab) pa
            )
  
            SELECT
                  pp.PERSON_PROFILE_ID AS PersonProfileId,
                  pp.LAST_NAME, FIRST_NAME,
                  (pp.LAST_NAME || ' , ' || pp.FIRST_NAME) AS InvestigatorName,
                  pp.ORG_NAME AS PrimaryEmployer,
                  pp.EMAIL_ADDR AS Email,
                  pp.PHONE_NUM AS Phone,
                  COUNT(*),
                  LISTAGG (ga.FULL_APPL_NUM, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "FullApplNums",
                  LISTAGG (ga.APPL_ID, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "ApplIds",
                  LISTAGG (ga.PROJECT_TITLE, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "ProjectTitles"
            FROM projects_CTE pcte
            JOIN APPL_PIS apis
            ON pcte.APPL_ID = apis.APPL_ID
            JOIN PERSON_PROFILES pp
            ON pp.PERSON_PROFILE_ID = apis.CONTACT_PI_PROFILE_PERSON_ID
            JOIN GRANT_APPLS ga
            ON ga.APPL_ID = pcte.APPL_ID
            WHERE 
            ga.APPL_TYPE_CODE &lt;&gt; 3  -- Non Supplement
            AND rownum &lt; 100
            GROUP BY PERSON_PROFILE_ID, LAST_NAME, FIRST_NAME, ORG_NAME, EMAIL_ADDR,PHONE_NUM  ;
  
    END GetPrincipalInvestigators;
  </code></pre>
  
  <p>Sorry this is difficult to explain.
  Maybe someone will have a better approach than this.</p>