I want to write a query so that it does functionality of listagg in oracle but I don't want to use it. Actually I don't want any function like lag and lead also. My table is-

Emp_id                          Emp_name                        Department
1                               bradley                             1 
2                                david                              1  
3                                 will                              2
4                                 john                              2
5                                 bale                              2
6                                 alan                              3
7                                 doug                              3
8                                 hagar                             4
9                                 carla                             4
10                                vashi                             1

I want output like this-

department                               emp_name
1                                        bradley,david, vashi
2                                         will ,john ,bale
3                                          alan, doug
4                                          hagar, carla

I have made a query for this but here I don't want to use sys_connect_by_path. My query is-

SELECT department,
       KEEP (DENSE_RANK LAST ORDER BY emp_name),',')AS employees
FROM   (SELECT department,
               ROW_NUMBER() OVER (PARTITION BY department ORDER BY emp_name) AS emp,
               ROW_NUMBER() OVER (PARTITION BY department ORDER BY emp_name) -1 AS prev
        FROM   employee4)
GROUP BY department
CONNECT BY prev = PRIOR emp AND department = PRIOR department
START WITH emp = 1;

Can anyone help me?

| improve this question | | | | |
  • 1
    CONNECT BY is not ANSI SQL either. LAG and LEAD are ANSI. – ypercubeᵀᴹ Aug 11 '15 at 9:41
  • I want a solution that is ANSI but one other hand without the use of lead and lag – Aditya Shrivastava Aug 11 '15 at 12:03
  • 1
    You still haven't told us why you want to avoid the listagg() function. "I want an ANSI solution" doesn't really explain it. Do you need this to be portable across different DBMS? The e.g. the one DBMS that does not support some kind of string aggregation, will not be able to use an ANSI solution because it uses a non-standard string concatenation operator (+ instead of ||). So again: why do you want to avoid listagg()? – a_horse_with_no_name Aug 11 '15 at 16:48
  • I agree with @a_horse_with_no_name (and that's why I voted to close this.) A valid ANSI solution will do you no good, if there are zero DBMS where you can test it. – ypercubeᵀᴹ Aug 11 '15 at 16:55

You can use a recursive common table expression. I have used a tmp table to enumerate the employees:

with tmp (empname, department, rn) as (
        select empname, department
             , row_number() over (partition by department 
                                  order by empname) as rn 
        from employees
), cte (empname, department, rn) as ( 
        select cast(empname as varchar(100)) as empname
             , department, rn 
        from tmp 
        where rn = 1 
        union all 
        select rtrim(c.empname) || ', ' || t.empname
             , t.department, c.rn+1 
        from tmp t
           , cte c 
        where t.department = c.department 
          and c.rn + 1 = t.rn
select empname, department 
from cte c1 
where rn = (select max(rn) 
            from cte c2 
            where c1.department = c2.department)
| improve this answer | | | | |
  • +1. Are the casts and the rtrim absolutely needed? – ypercubeᵀᴹ Aug 11 '15 at 16:26
  • Not sure in general, but DB2 determines the type of the result from the first leg of the union. The result is truncated if the the resulting string is longer than the string in the first iteration. – Lennart Aug 11 '15 at 16:30
  • Ah ok, makes sense. I think the ANSI is: trim(trailing ' ' from c.empname) – ypercubeᵀᴹ Aug 11 '15 at 16:31
  • Yes, you are right. I haven't got used to those, probably never will☺ – Lennart Aug 11 '15 at 16:48
  • And you may need with recursive. Not sure but I think it's mandatory when one of the ctes is using recursion (and that of course is implemented differently across DBMS. Some require it for recursion, some can do recursion without it, some others require that there is noRECURSIVE but they can still do recursion. it's just crazy.) – ypercubeᵀᴹ Aug 11 '15 at 16:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.