How to aggregate strings in R SQLDF?

Question

I have a dataset like this:

DEPTNO ENAME
   10 CLARK
   10 KING
   10 MILLER
   20 ADAMS
   20 FORD
   20 JONES

And I am trying to create a results like this:

DEPTNO AGGREGATED_ENAMES
   10 CLARK,KING,MILLER
   20 ADAMS,FORD,JONES

In Oracle this can be done by:

SQL> SELECT deptno
    ,LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
   FROM   emp
   GROUP  BY
   deptno;

How can I do this in SQLDF in R?

Or if it is not possible in R SQLDF, how can I do it in R?

Thanks! Parth

Answer 1

Use group_concat like this:

sqldf("select DEPTNO, group_concat(ENAME) ENAMES from emp group by DEPTNO")

giving:

  DEPTNO            ENAMES
1     10 CLARK,KING,MILLER
2     20  ADAMS,FORD,JONES