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
Thank you. You saved me a lot of time. I had such a headache over this.
Wanted to offer my thanks as well. Helped me figure out a query I was working on that concatenates EmployeeIDs of a person’s direct-reports into a single field.
Ade,
I’m stuck with a big project now, so I’m not sure how much time I would be able to spend to help you. If you need immediate help, try Oracle SQL forum at http://forums.oracle.com/forums/forum.jspa?forumID=75 . I’m sorry I couldn’t help a lot.
Hello Arun,
wondered if you could help?
I have this string
Expense_Inv_8- ExpenseInv_7- Exp001- Expense_Inv_6- Expense_Inv_5- Expense_Inv_4- Expense_Inv_3- Expense_Inv_2- Expense_inv1
and I need to display them in sections as explained below
Section 1 Section 2 section 3 section 4
Expense_Inv_8- ExpenseInv_7- Exp001;Expense_Inv_6- Expense_Inv_5;Expense_Inv_4- Expense_Inv_3;Expense_Inv_2;
need to split this string into 4 section seperated by ‘;’ and each section should be no more that 35 chracters, if null should end ;;;
Section 1 35 Character ended by;
section 2, broken off after Expense_Inv_5 because Expense_Inv_4 will take it over 35 chracters)
regards
Ade
Hi Arun,
Just wanted to say thanks for this information.
It really helped me in a query I was writting.
God bless
Thanks
Ade
Try checking explain plan and see if it can be tuned further. I think 30 secs is reasonable for 60,000 records, but i can’t say 100% sure since I don’t know the SQL and table layout and requirements. Since you have nested SQL statements, check one by one.
Thank you for the immediate reply Arun!
Here is my requirement,
TABLE: CUSTOMER_INPUT_FIELD
COLUMNS: RECORD_KEY RECORD_ID FIELD_ID FIELD_VALUE
DATA:
A001 NAME SATHISH
A001 DEPT ACCOUNTS
A001 DESG ACCOUNTANT
A002 NAME RAJESH
A002 DEPT HR
A003 DESG RECRUIT
I need to display it as
A001 NAME,DEPT,DESG SATHISH,ACCOUNTS,ACCOUNTANT
A002 NAME,DEPT,DESG RAJESH,HR,RECRUIT
With the help of ur query, I prepared a query for the same,
select RECORD_ID, substr(asg_fieldid,2) FIELD_ID, substr(asg_fieldvalue,2) FIELD_VALUE from ( select RECORD_ID, max(sys_connect_by_path(FIELD_VALUE,’;')) asg_fieldvalue, max(sys_connect_by_path(FIELD_ID,’;')) asg_fieldid from (select RECORD_ID, FIELD_VALUE, FIELD_ID, row_number() over (partition by RECORD_ID order by RECORD_ID) rnum from CUSTOMER_INPUT_FIELD) start with rnum = 1 connect by prior rnum = rnum-1 and prior RECORD_ID = RECORD_ID group by RECORD_ID order by RECORD_ID) order by RECORD_ID;
So… I am currently trying minimize the query processing time by setting max results and iterate for next result set.
Thank You
Jagadesan
Hi,
This query matched my exact reqiurement… Thank you very much!
But when i used this query, my sql editor takes an average time of 30 secs to display the result of 60000 records as 10000 records.
ie… 1 emp id has 6 categories
Is there any way to reduce the query processing time?
- Jagadesan
to convert multiple row to single row notusing pl/sql
c
—————–
A
B
C
D
output
c
—————–
A, B, C, D
and
c
—————–
A, B, C, D
output:
c
—————-
A
B
C
D
Lotus,
I would like to help, but there is not much detail in your requirements to help. I’m also in the process of clearing my work backlog. Try http://asktom.oracle.com for solutions.
I want the output as
PRDCT_FUND_INTRST_RATE_ID RNWL_RT_STRT_DT RNWL_RT_END_DT RNWL_RT
2 5/30/1985 5/30/1987 0.08
2 5/30/1987 5/30/1990 0.06
2 5/30/1990 5/30/1992 0.08
2 5/30/1992 5/30/1993 0.06
2 5/30/1993 5/30/1996 0.08
2 5/30/1996 0.01
Via SQL. No PLSQL code. Please help me to achieve this
duration roll up.
PRDCT_FUND_INTRST_RATE_ID RNWL_RT_STRT_DT RNWL_RT_END_DT RNWL_RT
2 5/30/1985 5/30/1986 0.08
2 5/30/1986 5/30/1987 0.08
2 5/30/1987 5/30/1988 0.06
2 5/30/1988 5/30/1989 0.06
2 5/30/1989 5/30/1990 0.06
2 5/30/1990 5/30/1991 0.08
2 5/30/1991 5/30/1992 0.08
2 5/30/1992 5/30/1993 0.06
2 5/30/1993 5/30/1994 0.08
2 5/30/1994 5/30/1995 0.08
2 5/30/1995 5/30/1996 0.08
2 5/30/1996 0.01
Thanks a lot, this was very helpful. – Citigroup.
Looks very cool.
How do you fit the concatenated data in the column that has a predefined size. If the original size is 50 and after you concatenate unknown number of assignments them it becomes 55 – would you not get a sql error for size? Would you make your column an unreasonably large all the time in case you have to concatenate?
That query was part of a view. Each employee might have a maximum of 3 or 4 assignments, so the column size is manageable.