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;
Rob van Wijk
18 August 2010 - 7:21 pm
Since COLLECT is an aggregate function, you can use another aggregate function to get the “topdog”:
Regards,
Rob.
Jeffrey Kemp
18 August 2010 - 8:34 pm
Thanks Rob, that’s much neater.