title: Concatenate two rows in the same column into one row (Oracle); use LISTAGG?
body: <p>I am using Oracle in Hyperion Studio.  I believe it is Oracle 11, but I am not sure how to check... this is all quite new to me, and I am very, very much a novice, I apologize.</p>
  
  <p>In any case, I am using a query to return a list of classes along with the names of the instructors teaching them.</p>
  
  <p>There are two issues that arise:</p>
  
  <ol>
  <li><p>Instructor name is split up into multiple columns.  I solved this easily using recursive CONCAT in a Computed Item:</p>
  
  <pre><code>CONCAT (Request.Instructor_Last_Name,CONCAT(', ',Request.Instructor_First_Name))
  </code></pre></li>
  <li>A course may have more than one instructor.  I want each course to only have one row in the results, so I figured I would try to show all instructors in a single concatenated field.</li>
  </ol>
  
  <p>To try to solve the second problem, I attempted using LISTAGG and CONCAT together in a Computed Item:</p>
  
  <pre><code>LISTAGG(CONCAT (Request.Instructor_Last_Name,CONCAT(', ',Request.Instructor_First_Name)), 'and ') 
  WITHIN GROUP (ORDER BY Request.Instructor_Last_Name DESC) "All Instructors"
  </code></pre>
  
  <p>This returns the error <code>ORA-00937: not a single-group group function</code>.</p>
  
  <p>This brought me to this question: <a href="https://stackoverflow.com/questions/24253262/listagg-query-ora-00937-not-a-single-group-group-function">LISTAGG Query &quot;ORA-00937: not a single-group group function&quot;</a>
  I tried variations on the solution there, with no success.  Here is one example I attempted:</p>
  
  <pre><code>LISTAGG(CONCAT (Request.Instructor_Last_Name,CONCAT(', ',Request.Instructor_First_Name)), 'and ') 
  WITHIN GROUP (ORDER BY Request.Instructor_Last_Name DESC) "All Instructors"
  FROM Request
  GROUP BY *
  </code></pre>
  
  <p>This returns the error:</p>
  
  <blockquote>
    <p>ORA-00936: missing expression </p>
  </blockquote>
  
  <p>I also tried replacing </p>
  
  <pre><code>LISTAGG(CONCAT (Request.Instructor_Last_Name,CONCAT(', ',Request.Instructor_First_Name)), 'and ') 
  </code></pre>
  
  <p>with </p>
  
  <pre><code>LISTAGG(Request.Instructor_Last_Name || ', ' || Request.Instructor_First_Name,'and ') 
  </code></pre>
  
  <p>based on something else I read, but that didn't seem to make any difference (I'm not even sure if that would work anyway).</p>
  
  <p>This has exhausted me.  I'm sure at this point the answer must be eluding me only due to my inexperience, so any help is hugely appreciated...</p>
  
  <hr>
  
  <p>Here is a very stripped-down version of the query, using the code vkp suggested (these are Banner tables, as some may recognize):</p>
  
  <pre><code>SELECT DISTINCT 
  AL1.SFRSTCR_TERM_CODE, AL1.SFRSTCR_CRN, 
  AL3.SPRIDEN_FIRST_NAME, AL3.SPRIDEN_LAST_NAME, 
  CONCAT ((AL3.SPRIDEN_LAST_NAME),CONCAT(', ',(AL3.SPRIDEN_FIRST_NAME))),   
  select Student_Registration_CRN,
  LISTAGG((Instructor_Last_Name||', '||Instructor_First_Name), 'and '))
  WITHIN GROUP (ORDER BY Instructor_Last_Name DESC) "All Instructors"
  FROM Request
  group by Student_Registration_CRN 
  FROM SATURN.SFRSTCR AL1, SATURN.SIRASGN AL2, SATURN.SPRIDEN AL3 
  WHERE ( AL1.SFRSTCR_TERM_CODE = AL2.SIRASGN_TERM_CODE (+) 
  AND  AL1.SFRSTCR_CRN = AL2.SIRASGN_CRN (+) 
  AND  AL2.SIRASGN_PIDM = AL3.SPRIDEN_PIDM (+))  
  AND ((AL3.SPRIDEN_CHANGE_IND IS NULL AND AL1.SFRSTCR_TERM_CODE='201660'))
  </code></pre>
  
  <p>This produces the error: </p>
  
  <blockquote>
    <p>ORA-00936: missing expression </p>
  </blockquote>
  
  <p>I have used aliases for some of the column names, so I tried the suggested code without aliases, but that didn't seem to make a difference.</p>
  
  <hr>
  
  <p>I have tried using Boneist's suggested SQL, by importing the .SQL file directly, rather than using the visual builder and Computed Items.  Here is what I tried to run:</p>
  
  <pre><code>select distinct
     al1.sfrstcr_term_code,
     al1.sfrstcr_crn,
     al3.spriden_first_name,
     al3.spriden_last_name,
     al3.spriden_last_name||', '||al3.spriden_first_name,
     (select   sfrstcr_crn,
               listagg(spriden_last_name || ', ' || spriden_first_name, 'and ')
                 within group (order by spriden_last_name desc)
      from     request req
      where    sfrstcr_crn = al1.sfrstcr_crn
      group by sfrstcr_crn) "All Instructors"
  from   saturn.sfrstcr al1
     left outer join saturn.sirasgn al2 on (al1.sfrstcr_term_code = al2.sirasgn_term_code
                                            and al1.sfrstcr_crn = al2.sirasgn_crn)
     left outer join saturn.spriden al3 on (al2.sirasgn_pidm = al3.spriden_pidm(+))
  where  al3.spriden_change_ind is null
  and    al1.sfrstcr_term_code = '201660';
  </code></pre>
  
  <p>Unfortunately, this returns the error:</p>
  
  <blockquote>
    <p>ORA-00933: SQL command not properly ended</p>
  </blockquote>
  
  <p>I thought, based on some searching, that it might somehow be that the quotes were not importing correctly, so I tried something even more basic:</p>
  
  <pre><code>select distinct
     al1.sfrstcr_term_code,
     al1.sfrstcr_crn,
     al3.spriden_first_name,
     al3.spriden_last_name
  from   saturn.sfrstcr al1
     left outer join saturn.sirasgn al2 on (al1.sfrstcr_term_code = al2.sirasgn_term_code
                                            and al1.sfrstcr_crn = al2.sirasgn_crn)
     left outer join saturn.spriden al3 on (al2.sirasgn_pidm = al3.spriden_pidm(+))
  where  al3.spriden_change_ind is null;
  </code></pre>
  
  <p>But I still get the same error...   I tried searching for what else could cause it, but didn't find anything helpful... I am not sure what else to try at this point.</p>