CODEWITHSUNDEEP
sqloracle-database
user10753505
user10753505

Reputation: 99

Merge different record of same employee in oracle

I need to display a data in such a way that for single employee, there is multiple charge i,e current or Additional charge and, I need to merge the column for those employee who are currently having additional charge along with current charge, i wrote a query, which fetches the record.

       SELECT 
DISTINCT ost.EMP_ID,
E.name emp_name,
E.EMP_CODE emloyee_code,
L.NAME Official_loc,
DEP.name DEPARTMENT_NAME,
LISTAGG(DEP.name,'|') WITHIN GROUP (ORDER BY DEP.name )DEPARTMENT_NAME,
D.NAME DESIGNATION_NAME, 
ost.UPDATE_BY,
ost.UPDATE_DATE,
ost.UPDATE_BY_NAME,
LISTAGG(ost.CHARGE,'|')WITHIN GROUP (ORDER BY ost.CHARGE) AS CHARGE,
--ost.CHARGE,
ost.OFFICIAL_LOCATION_ID,
ost.STATUS 
FROM ORG_STRUCTURE_TAGGING ost
JOIN EMPLOYEE E 
ON E.EMP_ID=ost.EMP_ID
LEFT JOIN DESIGNATION D
ON D.DESIGNATION_ID=ost.DESIGNATION_ID
LEFT JOIN DEPARTMENT DEP
ON DEP.DEPARTMENT_ID=ost.DEPARTMENT_ID
LEFT JOIN LOCATION L
ON L.LOCATION_ID=ost.OFFICIAL_LOCATION_ID
WHERE ost.STATUS=1 
group by ost.EMP_ID, E.name, E.EMP_CODE, L.NAME, DEP.name, 
ost.DESIGNATION_ID, D.NAME, ost.UPDATE_BY, ost.UPDATE_DATE, ost.UPDATE_BY_NAME, 
ost.OFFICIAL_LOCATION_ID, ost.STATUS

OUTPUT:

614 Abraham Oommen  1541032 Corporate Office    National Events Computer Operator   11-JAN-19   Super Administrator Additional Charge
633 Mukesh R Sharma 1051009 Corporate Office    Content Management (National Events)    Executive Director  14-JAN-19   Super Administrator Current Charge
686 Nousara Khadka  1051019 Corporate Office    Content Management (National Events)    Executive Engineer  11-JAN-19   Super Administrator Current Charge
688 Prem Lal Namdeo 1051029 Corporate Office    Pre & Post Allotment    Computer Operator   11-JAN-19   Super Administrator Current Charge
364 Kumari Sapna    1031036 Corporate Office    Events & Roadshows (International Events)   Manager 15-JAN-19   Super Administrator Additional Charge
364 Kumari Sapna    1031036 Corporate Office    Events & Roadshows (International Events)   Computer Operator   14-JAN-19   Super Administrator Current Charge
614 Abraham Oommen  1541032 Corporate Office    Content Management (International Events)   Assistant Grade 2   11-JAN-19   Super Administrator Current Charge
469 Pooja Bhimte    1071025 Corporate Office    Content Management (International Events)   Chief General Manager   14-JAN-19   Super Administrator Current Charge
242 Vijay Sugandhi  1023014 Corporate Office    Allotment   Executive Engineer  15-JAN-19   Super Administrator Current Charge
686 Nousara Khadka  1051019 Corporate Office    Procurement Senior Accountant   11-JAN-19   Super Administrator Additional Charge

As per output for single employee we have two designation and charge, I want to merge it as single column and keep their values comma separated.

for Eg: EMP ID 614 has two record

EMP ID, EMP_NAME,    EMP_CODE,     LOCATION,         DESIGNATION,  ASSIGNED SECTION
    614 Abraham Oommen  1541032 Corporate Office    Content Management (International Events),National Events   "Assistant Grade 2-->current Charge,Computer Operator--> Additional Charge"

DESIGNATION should be output as "Content Management (International Events),National Events Computer Operator"

ASSIGNED_SECTION should be output as "Assistant Grade 2-current Charge,Computer Operator- Additional Charge"

As of now, those values belongs to same employee but are in different columns, i want to merge it, kindly suggest me the solution for it. 

TABLE STRUCTURE:

ORG_STRUCTURE_TAGGING_ID    NUMBER  Yes     1   
EMP_ID  NUMBER  Yes     2   
DEPARTMENT_ID   VARCHAR2(2 BYTE)    Yes     3   
DESIGNATION_ID  VARCHAR2(50 BYTE)   Yes     4   
UPDATE_BY   NUMBER  Yes     5   
UPDATE_DATE DATE    Yes     6   
UPDATE_BY_NAME  VARCHAR2(100 BYTE)  Yes     7   
CHARGE  VARCHAR2(100 BYTE)  Yes     8   
OFFICIAL_LOCATION_ID    VARCHAR2(2 BYTE)    Yes     9   
STATUS  NUMBER  Yes     10  
ENTRY_BY    NUMBER  Yes     11  
ENTRY_DATE  DATE    Yes     12  
APPROVED_BY NUMBER  Yes     13  
APPROVED_DATE   DATE    Yes     14

Upvotes: 1

Views: 94

Answers (2)

Alex Poole
Alex Poole

Reputation: 191285

Based on the more complicated output you said you were looking for, you seem to want something like this:

select
  ost.emp_id,
  e.name emp_name,
  e.emp_code emloyee_code,
  l.name official_loc,
  listagg(dep.name, ',')
    within group (order by dep.name) as departments,
  listagg(d.name || '-' || ost.charge, ',')
    within group (order by ost.charge desc, d.name) as designations,
  ost.update_by,
  ost.update_date,
  ost.update_by_name,
  ost.status 
from org_structure_tagging ost
join employee e on e.emp_id=ost.emp_id
left join designation d on d.designation_id=ost.designation_id
left join department dep on dep.department_id=ost.department_id
left join location l on l.location_id=ost.official_location_id
where ost.status=1 
group by 
  ost.emp_id, 
  e.name, 
  e.emp_code, 
  l.name, 
  ost.update_by, 
  ost.update_date, 
  ost.update_by_name, 
  ost.status;

I may have the column names in the wrong order as it isn't clear which is which from your output. But this is doing a simple aggregation of department name, and a slightly more complicated aggregation of the concatenated designation and charge values.

Or to prevent duplicates, use a subquery which applies distinct before aggregating:

select
  emp_id,
  emp_name,
  employee_code,
  official_loc,
  listagg(department, ',')
    within group (order by department) as departments,
  listagg(designation || '-' || charge, ',')
    within group (order by charge desc, designation) as designations,
  update_by,
  update_date,
  update_by_name,
  status 
from(
  select distinct
    ost.emp_id,
    e.name emp_name,
    e.emp_code employee_code,
    l.name official_loc,
    dep.name as department,
    d.name as designation,
    ost.charge,
    ost.update_by,
    ost.update_date,
    ost.update_by_name,
    ost.status 
  from org_structure_tagging ost
  join employee e on e.emp_id=ost.emp_id
  left join designation d on d.designation_id=ost.designation_id
  left join department dep on dep.department_id=ost.department_id
  left join location l on l.location_id=ost.official_location_id
  where ost.status=1
)
group by 
  emp_id, 
  emp_name, 
  employee_code, 
  official_loc,
  update_by, 
  update_date, 
  update_by_name, 
  status;

Upvotes: 2

GMB
GMB

Reputation: 222492

Here is a simplified version of your query, whose results should match your expected output.

As explained in the comments, every non-aggregated column must appear in the GROUP BY clause, while aggregated columns should not appear in the GROUP BY. In the query that you have showed, column DEP.name is aggregated with LISTAGG but also present in the GROUP BY : as a consequence, no aggregation actually happens.

I removed all SELECTed fields that do no appear in your expected result (such as CHARGE for example) and used LISTAGG to aggregate :

SELECT DISTINCT 
    ost.EMP_ID,
    E.name emp_name,
    E.EMP_CODE emloyee_code,
    L.NAME Official_loc,
    LISTAGG(DEP.name,',') WITHIN GROUP (ORDER BY DEP.name) DESIGNATION,
    LISTAGG(D.name, ',') WITHIN GROUP (ORDER BY D.name) ASSIGNED_SECTION
FROM 
    ORG_STRUCTURE_TAGGING ost
    JOIN EMPLOYEE E ON E.EMP_ID=ost.EMP_ID
    LEFT JOIN DESIGNATION D ON D.DESIGNATION_ID=ost.DESIGNATION_ID
    LEFT JOIN DEPARTMENT DEP ON DEP.DEPARTMENT_ID=ost.DEPARTMENT_ID
    LEFT JOIN LOCATION L ON L.LOCATION_ID=ost.OFFICIAL_LOCATION_ID
WHERE 
    ost.STATUS=1 
GROUP BY 
    ost.EMP_ID,
    E.name,
    E.EMP_CODE,
    L.NAME

Upvotes: 2

Related Questions