How to perform listagg functionality without using it

Question

1.00/5 (2 votes)

See more:

I just want to create a query so that it does the same function as listagg in oracle but I dont want to use wm_cocat, lag or lead. My table is-

Emp_id - emp_name - department
1 - alan - 1
2 - bradley - 1
3 - sarah - 2
4 - will 2
5 - bale - 2
6 - doug - 3
7 - hagar - 3
8 - carla - 4
9 - david - 4
10 - vashi - 1

I have a query which is working but I am not quite sure how it work. The query is-

{
SELECT department,
LTRIM(max(SYS_CONNECT_BY_PATH(emp_name,','))
KEEP (DENSE_RANK LAST ORDER BY emp_name),',')AS employees
FROM (SELECT department,
emp_name,
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 department=prior department AND prev = PRIOR emp
START WITH emp = 1; }

Can anyone explain me how this query works and is their any alternative solution for that?

Posted 11-Aug-15 0:57am

Member 11898509

Updated 11-Aug-15 3:28am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11898509 · Accepted Answer · 2015-08-13T23:41:00

WITH COMMON TABLE EXPRESSION we can do something like this-

SQL

with t (emp_name, department, rn) as ( select emp_name, department,row_number()
over (partition by department order by emp_name) as rn
        from employee8),
        c (emp_name, department, rn) as (select  emp_name,department, rn from t where rn = 1
        union all
        select (c1.emp_name) ||', '|| t1.emp_name, t1.department, c1.rn+1
        from t t1 , c c1
        where t1.department = c1.department
          and c1.rn + 1 = t1.rn
)
select emp_name, department
from c c1
where rn = (select max(rn)
            from c c2
            where c1.department = c2.department)
            order by department;

By using dense_rank and rownumber we can do it like-

SQL

with t (emp_name, department, rn) as ( select emp_name, department,row_number()
over (partition by department order by emp_name) as rn
        from employee8),
        c (emp_name, department, rn) as (select  emp_name,department, rn from t where rn = 1
        union all
        select (c1.emp_name) ||', '|| t1.emp_name, t1.department, c1.rn+1
        from t t1 , c c1
        where t1.department = c1.department
          and c1.rn + 1 = t1.rn
)
select emp_name, department
from c c1
where rn = (select max(rn)
            from c c2
            where c1.department = c2.department)
            order by department;