Arun's Blog

One of the project I’m working on has a requirement where if an employee has multiple assignments (multiple rows in the assignment table), then the SQL should consolidate his assignments into one row, with each column holding multiple values (for each row) with a delimiter.

For eg: Here is a sample table

SQL> desc test_asg
Assignment_id number(15)
Title varchar2(50)  — Job title
Emp_cat varchar2(2)  — Employment category

Here is the data in the table:

ASSIGNMENT_ID    TITLE         EMP_CAT
100              Comp info     AP
100              Technician    AP
101              Faculty       AP
102              Teaching      CS
102              Grad Assist   CS
102              Secretary     AP
103              Director      AP

The output requirement is

ASSIGNMENT_ID    JOB_TITLE                         EMP_CATEGORY
100              Comp info;Technician              AP;AP
101              Faculty                           AP
102              Teaching;Grad Assist;Secretary    CS;CS;AP
103              Director                          AP

Here is the SQL I wrote to get the output.

select assignment_id, substr(asg_title,2) JOB_TITLE, substr(asg_empcat,2) EMP_CATEGORY
from (
select assignment_id, max(sys_connect_by_path(title,’;')) asg_title, max(sys_connect_by_path(emp_cat,’;')) asg_empcat
from (
select assignment_id, title, emp_cat, row_number() over (partition by assignment_id order by assignment_id) rnum
from test_asg
)
start with rnum = 1
connect by prior rnum = rnum-1 and prior assignment_id = assignment_id
group by assignment_id
order by assignment_id
)
order by assignment_id;

The SYS_CONNECT_BY_PATH function returns the list of titles concatenated together and list of emp_cat connected together for each assignment id. The Start with .. Connect by clause is used to select data having hierarchical relationship.

Technorati Tags: Oracle, SQL, SYS_CONNECT_BY_PATH, Start With, Connect By, Assignment

Share on Facebook

Bookmark It

Hide Sites