Monday, June 1, 2020

How to achieve listagg effect in pre-11g Oracle database and listagg example



Version 1

select department_id, rtrim(first_name,',') enames
from ( select department_id, first_name, rn
       from employees
       model 
       partition by (department_id)
       dimension by (
         row_number() over (partition by department_id order by first_name) rn
       )
       measures (cast(first_name as varchar2(300)) first_name)
       rules (
        first_name[any] order by rn desc = first_name[cv()]||','||first_name[cv()+1]
        )
)
where rn = 1
order by department_id
;

Version 2

select department_id, substr(max(sys_connect_by_path(first_name,',')),2) members
from (select department_id, first_name, 
    row_number() over (partition by department_id order by department_id) rn
    from employees)
start with rn = 1
connect by prior rn = rn-1
and prior department_id = department_id
group by department_id
;

Version 3

-- 11g Orace listagg function
select department_id, listagg(first_name, ',') within group (order by department_id) members
from employees
group by department_id
;

Version 4

-- 19c new features: distinct clause to remove duplicates
select department_id, listagg(distinct first_name, ',') within group (order by department_id) members
from employees
group by department_id
;

1 comment:

  1. it's really cool blog. Linking is very useful thing.you have really helped

    360digitmg data science course

    ReplyDelete