Never satisfied

So I followed the great advice here to use the new COLLECT function in Oracle 10g to solve the common string-aggregation-in-SQL problem. I chose this solution over the others because it sorts the results as well, which was a user requirement. This is because the COLLECT function optionally takes an ORDER BY clause – even though the 10g documentation forgot to mention this. The 11g documentation was updated to include the additional syntax option, as well as the fact that COLLECT supports the DISTINCT (or UNIQUE) keyword as well to remove duplicates – testing indicates that this works in 10g as well.

This means that if I define a suitable type varchar2_ntt and a suitable function ntt_to_string, I can write a query like this:

SELECT dept,
       ntt_to_string(
          CAST(
            COLLECT(ename ORDER BY ename)
          AS varchar2_ntt)
       ) AS ename_list
FROM emp
GROUP BY dept;

That works fine. But now I want to combine this with some analytic functions, like this:

SELECT DISTINCT
       dept,
       ntt_to_string(
          CAST(
            COLLECT(ename ORDER BY ename)
          AS varchar2_ntt)
       ) AS ename_list,
       FIRST_VALUE(ename)
          OVER (PARTITION BY dept
                ORDER BY sal DESC) AS topdog
FROM emp;

This doesn’t work because (a) the COLLECT requires a GROUP BY; and (b) the analytic function cannot be used along with a GROUP BY. What I’d expect to be able to do is use an analytic version of the COLLECT:

SELECT DISTINCT
       dept,
       ntt_to_string(
          CAST(
            COLLECT(ename
                    PARTITION BY dept
                    ORDER BY ename)
          AS varchar2_ntt)
       ) AS ename_list,
       FIRST_VALUE(ename)
          OVER (PARTITION BY dept
                ORDER BY sal DESC) AS topdogFROM emp;

Us SQL developers are never satisfied, are we?

At this stage my best solution for this is to use a CTE:

WITH q AS (SELECT dept, ename, sal FROM emp)
SELECT DISTINCT
       dept,
       (SELECT ntt_to_string(
                 CAST(
                   COLLECT(q2.ename ORDER BY q2.ename)
                 AS varchar2_ntt)
               )
        FROM q q2
        WHERE q2.dept = q.dept
       ) AS ename_list,
       FIRST_VALUE(ename)
          OVER (PARTITION BY dept
                ORDER BY sal DESC) AS topdog
FROM q;

Jeffrey Kemp 18 August 2010

aggregate-functions / oracle-docs / SQL /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.