How can I concatenate 3 rows into single row with comma seperation in oracle table?

Question

1.00/5 (1 vote)

See more:

Below is my query

SQL

SELECT 
PR_EMPLOYEE_MASTER.SALUTE,
PR_EMPLOYEE_MASTER.FIRST_NAME,
PR_EMPLOYEE_MASTER.MIDDLE_NAME,
PR_EMPLOYEE_MASTER.LAST_NAME,  
PR_EMPLOYEE_MASTER.FATHER_TITLE, 
PR_EMPLOYEE_MASTER.FATHER_NAME, 
PR_EMPLOYEE_MASTER.HUSB_TITLE HUSBAND_TITLE, 
PR_EMPLOYEE_MASTER.HUSB_NAME HUSBAND_NAME, 
PR_EMPLOYEE_MASTER.DATE_OF_BIRTH,
PR_EMPLOYEE_MASTER.PLACE_OF_BIRTH, 
PR_EMPLOYEE_MASTER.GENDER,
PR_EMPLOYEE_MASTER.MARITAL_STATUS, 
PR_EMPLOYEE_MASTER.ANN_DATE, 
PR_EMPLOYEE_MASTER.MEDICALIM_NO, 
PR_EMPLOYEE_MASTER.AMFI_CLEARED, 
PR_EMPLOYEE_MASTER.AMFI_REGNO, 
PR_EMPLOYEE_MASTER.AMFI_YEAR, 
PR_EMPLOYEE_MASTER.BLOOD_GROUP,
PR_EMPLOYEE_MASTER.NATIONALITY,
PR_EMPLOYEE_MASTER.ACTIVE, 
PR_EMPLOYEE_MASTER.IMG_FILE_NAME,
PR_EMPLOYEE_MASTER.PASSPORT_FILE_NAME,
PR_EMPLOYEE_MASTER.RESUME_FILE_NAME,
PR_EMPLOYEE_MASTER.PAN_FILE_NAME,
PR_EMPLOYEE_MASTER.LICENSE_FILE_NAME,
PR_EMPLOYEE_MASTER.PASSPORT_NO,
PR_EMPLOYEE_MASTER.PASSPORT_ISSUE,
PR_EMPLOYEE_MASTER.PASSPORT_VALID_DATE PASSPORT_VALID,
PR_EMPLOYEE_MASTER.DRIVING_LICENSE_NO, 
PR_EMPLOYEE_MASTER.DRIVING_LICENSE_ISSUE DRIVE_ISSUE,
PR_EMPLOYEE_MASTER.DRIVING_LICENSE_VALID_DATE DRIVE_VALID,
PR_EMPLOYEE_MASTER.EMP_NO,
PR_EMPLOYEE_MASTER.EMP_NO_ALIAS,RETIRE_AGE HDN_RET_AGE,
PR_EMPLOYEE_MASTER.RETIREMENT_DAY,
FN_UD_MONTHNAME(PR_EMPLOYEE_MASTER.RETIREMENT_MONTH,'MON') RETIREMENT_MONTH,
PR_EMPLOYEE_MASTER.RETIREMENT_YEAR, 
PR_EMPLOYEE_MASTER.CONFIRM_DATE,
PR_EMPLOYEE_MASTER.DATE_OF_JOIN,
PR_EMPLOYEE_MASTER.PF_START_DATE,
PR_EMPLOYEE_MASTER.PREV_SERV_INMONTHS PREV_SERV_INMONTH,
PR_EMPLOYEE_MASTER.REG_NO, 
PR_EMPLOYEE_MASTER.BOND_PERIOD, 
FN_UD_ENDECRYPTNUM(PR_EMPLOYEE_MASTER.BOND_AMOUNT,2, 6438 , 47 ) BOND_AMOUNT, 
PR_EMPLOYEE_MASTER.BANK_ACCOUNT_NO,
PR_EMPLOYEE_MASTER.PAN_NO,PR_EMPLOYEE_MASTER.PF_NO,PR_EMPLOYEE_MASTER.ESIC_NO, 
PR_EMPLOYEE_MASTER.COMPENSATION,
PR_EMPLOYEE_MASTER.COMP_PROFICIENCY,
PR_EMPLOYEE_MASTER.PROF_OBJECTIVE,
PR_EMPLOYEE_MASTER.MEDICAL_DETAILS,
PR_EMPLOYEE_MASTER.PAY_TYPE,
PR_EMPLOYEE_MASTER.REMARKS,
PR_EMPLOYEE_MASTER.REPORTING_TO_ID,
PR_EMPLOYEE_MASTER.PERSONAL_EMAIL P_EMAIL,
PR_EMPLOYEE_MASTER.OFFICIAL_EMAIL O_EMAIL,
PR_EMPLOYEE_MASTER.ALT_CONTCT_NO ALT_CONTCT_NO,
PR_EMPLOYEE_MASTER.MOBILE_NO MOBILE_NO,
PR_EMPLOYEE_MASTER.COUNTRY_CODE,
PR_EMPLOYEE_MASTER.RPT_TO_TYPE RT_SELECT,
PR_EMPLOYEE_MASTER.RPT_BY_TYPE RB_SELECT,
PR_EMPLOYEE_MASTER.DIRECT_TEL_NO DIRECT_TELE_NO,
PR_EMPLOYEE_MASTER.EXTENSION,
PR_EMPLOYEE_MASTER.NTID,
PR_EMPLOYEE_MASTER.SAL_STRUCT_BASE DrpDwnSalStrutBase,
PR_EMPLOYEE_MASTER.PR_SHIFT_NAME_MASTER_ID HDN_PR_SHIFT_NAME_MASTER_ID ,
PR_GRADE_MASTER.ID PR_GRADE_MASTER_ID, 
PR_GRADE_MASTER.NAME EMP_GRADE,PR_EMPLOYEE_MASTER.PROB_PERIOD, 
PR_CATEGORY_MASTER.ID PR_CATEGORY_MASTER_ID, 
PR_CATEGORY_MASTER.NAME CATEGORY_NAME, 
LOCATION_MASTER.ID LOCATION_ID, 
LOCATION_MASTER.LOCATION_NAME LOCATION, 
PR_DESIG_MASTER.ID PR_DESIG_MASTER_ID, 
PR_DESIG_MASTER.NAME DESIG_NAME, 
PR_SUBDESIG_MASTER.ID PR_SUBDESIG_MASTER_ID, 
PR_ACTIVITY_MASTER.ID PR_ACTIVITY_MASTER_ID, 
PR_SUBDESIG_MASTER.NAME SUBDESIG_NAME, 
PR_ACTIVITY_MASTER.NAME ACTIVITY_NAME, 
PR_DESIG_MASTER.LEVEL_NO LEVEL_NO,
FN_UD_NVLN(PR_RELIGION_MASTER.ID,'')PR_RELIGION_MASTER_ID,
FN_UD_NVLC(PR_RELIGION_MASTER.NAME,'')RELIGION_NAME, 
FN_UD_NVLN(PR_CASTE_MASTER.ID,'')PR_CASTE_MASTER_ID,
FN_UD_NVLC(PR_CASTE_MASTER.CASTE_NAME,'')CASTE_NAME, 
SUBDEPARTMENT_MASTER.ID SUBDEPARTMENT_MASTER_ID, 
SUBDEPARTMENT_MASTER.NAME SUBDEPARTMENT_NAME, 
SUB_SUBDEPARTMENT_MASTER.ID SUB_SUBDEPARTMENT_MASTER_ID, 
SUB_SUBDEPARTMENT_MASTER.NAME SUB_SUBDEPARTMENT_NAME, 
DEPARTMENT_MASTER.ID DEPARTMENT_MASTER_ID, 
DEPARTMENT_MASTER.NAME DEPARTMENT_NAME, 
BANK_MASTER.ID BANK_MASTER_ID, 
BANK_MASTER.BANK_NAME, 
BANK_BRANCH_MASTER.ID BANK_BRANCH_MASTER_ID, 
BANK_BRANCH_MASTER.BANK_BRANCH_NAME, 
fn_ud_nvln(PR_EMPLOYEE_MASTER.PR_EMPLOYEE_REFNO_ID,0) PR_EMPLOYEE_REFNO_ID, 
fn_ud_nvlc(PR_EMPLOYEE_REFNO.NAME,'') EMPLOYEE_REF_NAME, 
fn_ud_nvlc(REF_NAME,'') REF_NAME, 
PR_EMPLOYEE_MASTER.PLACE_BROUGHT_UP ,PR_EMPLOYEE_MASTER.PASSION ,PR_EMPLOYEE_MASTER.CARE_ABOUT ,PR_EMPLOYEE_MASTER.INTERESTS ,PR_EMPLOYEE_MASTER.HIDDEN_FACTS ,PR_EMPLOYEE_MASTER.FAN ,PR_EMPLOYEE_MASTER.ONE_WORD_DESCRIPTION, PR_EMPLOYEE_MASTER.PAY_CYCLE,PR_EMPLOYEE_MASTER.EMP_STATUS STATUS,PR_EMPLOYEE_MASTER.PENSION_NUMBER, 
PR_GRADE_MASTER.MIN_AGE, PR_GRADE_MASTER.MAX_AGE,
HR_LOOKUP.DESCRIPTION CASTE_GROUP, 
PR_EMPLOYEE_MASTER.HOBBY_REMARKS, 
PR_EMPLOYEE_MASTER.ACHIEVE_REMARKS, 
PR_EMPLOYEE_MASTER.PR_STATEHOOD_MASTER_ID STATEHOOD_ID,
PR_STATEHOOD_MASTER.NAME STATEHOOD_NAME,
PR_EMPLOYMENT_TYPE_MASTER.ID PR_EMPLOYMENT_TYPE_MASTER_ID, 
PR_EMPLOYMENT_TYPE_MASTER.NAME EMPLOYMENT_TYPE,
PR_EMPLOYEE_MASTER.DOJ_FPS FPS_DATE_OF_JOIN,
PR_EMPLOYEE_MASTER.DOJ_EPS EPS_DATE_OF_JOIN, 
PR_EMPLOYEE_MASTER.BANK_BRANCH_CODE, 
PR_EMPLOYEE_MASTER.SHIFT_BASED,
PR_EMPLOYEE_MASTER.PAYSLIP_EMAIL ALLOW_PAYSLIP_EMAIL,
PR_EMPLOYEE_MASTER.ABOVE_TOTAL AboveTotal,
PR_EMPLOYEE_MASTER.EXTERNAL_EXPERIENCE ExternalExperience,
PR_EMPLOYEE_MASTER.NOTICE_PERIOD,
PR_EMPLOYEE_MASTER.NOTICE_PERIOD_PROB, 
PR_EMPLOYEE_MASTER.OLD_EMPLOYEE_MASTER_ID OLD_PR_EMPLOYEE_MASTER_ID,
PR_EMPLOYEE_MASTER.PR_VENDOR_MASTER_ID VENDOR_ID,
VENDOR_MASTER.VENDOR_NAME ,
PR_EMPLOYEE_MASTER.VENDOR_ASC_CODE VENDOR_CODE,
PR_EMPLOYEE_MASTER.ASC_NOTICE_PERIOD, 
PR_EMPLOYEE_MASTER.ATTENDANCE_MARKING, 
PR_EMPLOYEE_MASTER.PAYEE_NAME,
PR_EMPLOYEE_MASTER.REPORTING_TO_ID2 REPORTING_TO_ID_SEC, 
PR_EMPLOYEE_MASTER.PR_OFFER_ID, 
FN_UD_CONCAT(PR_OFFER.SALUTE,PR_OFFER.FIRST_NAME,PR_OFFER.Middle_Name,PR_OFFER.LAST_NAME) CANDIDATE_NAME, 
PR_JOB_PROFILE_MST.NAME JOB_PROFILE 
FROM  
PR_GRADE_MASTER, 
PR_CATEGORY_MASTER, 
LOCATION_MASTER, 
PR_DESIG_MASTER, 
Pr_Desig_Mst_Prf_Det,
PR_JOB_PROFILE_MST,
(((((((((((((((PR_EMPLOYEE_MASTER inner join DEPARTMENT_MASTER on
PR_EMPLOYEE_MASTER.DEPARTMENT_MASTER_ID = DEPARTMENT_MASTER.ID)
LEFT OUTER JOIN SUBDEPARTMENT_MASTER ON 
PR_EMPLOYEE_MASTER.SUBDEPARTMENT_MASTER_ID = SUBDEPARTMENT_MASTER.ID) 
LEFT OUTER JOIN SUB_SUBDEPARTMENT_MASTER ON 
PR_EMPLOYEE_MASTER.SUB_SUBDEPARTMENT_MASTER_ID = SUB_SUBDEPARTMENT_MASTER.ID) 
LEFT OUTER JOIN PR_RELIGION_MASTER ON PR_EMPLOYEE_MASTER.PR_RELIGION_MASTER_ID=PR_RELIGION_MASTER.ID) 
LEFT OUTER JOIN PR_CASTE_MASTER ON PR_EMPLOYEE_MASTER.PR_CASTE_MASTER_ID=PR_CASTE_MASTER.ID) 
LEFT OUTER JOIN BANK_MASTER ON PR_EMPLOYEE_MASTER.BANK_MASTER_ID=BANK_MASTER.ID AND BANK_MASTER.RSMART_CLIENT_MST_ID= 25252  ) 
LEFT OUTER JOIN BANK_BRANCH_MASTER ON PR_EMPLOYEE_MASTER.BANK_BRANCH_MASTER_ID=BANK_BRANCH_MASTER.ID AND BANK_BRANCH_MASTER.RSMART_CLIENT_MST_ID= 25252  ) 
LEFT OUTER JOIN PR_EMPLOYEE_REFNO ON PR_EMPLOYEE_MASTER.PR_EMPLOYEE_REFNO_ID = PR_EMPLOYEE_REFNO.ID ) 
LEFT OUTER JOIN PR_SUBDESIG_MASTER ON PR_EMPLOYEE_MASTER.PR_SUBDESIG_MASTER_ID = PR_SUBDESIG_MASTER.ID ) 
LEFT OUTER JOIN PR_ACTIVITY_MASTER ON PR_EMPLOYEE_MASTER.PR_ACTIVITY_MASTER_ID = PR_ACTIVITY_MASTER.ID ) 
LEFT OUTER JOIN HR_LOOKUP ON HR_LOOKUP.LOOKUP_TYPE='CASTGRP' AND HR_LOOKUP.LOOKUP_CODE=PR_CASTE_MASTER.CASTE_GROUP) 
LEFT OUTER JOIN PR_STATEHOOD_MASTER ON PR_EMPLOYEE_MASTER.PR_STATEHOOD_MASTER_ID = PR_STATEHOOD_MASTER.ID ) 
LEFT OUTER JOIN PR_EMPLOYMENT_TYPE_MASTER ON PR_EMPLOYEE_MASTER.PR_EMPLOYMENT_TYPE_MASTER_ID = PR_EMPLOYMENT_TYPE_MASTER.ID ) 
LEFT OUTER JOIN VENDOR_MASTER ON PR_EMPLOYEE_MASTER.PR_VENDOR_MASTER_ID=VENDOR_MASTER.ID) 
LEFT OUTER JOIN PR_OFFER ON PR_EMPLOYEE_MASTER.PR_OFFER_ID=PR_OFFER.ID)                                                 
WHERE  
PR_EMPLOYEE_MASTER.ID =  6438
AND PR_EMPLOYEE_MASTER.PR_GRADE_MASTER_ID = PR_GRADE_MASTER.ID 
AND PR_EMPLOYEE_MASTER.EMP_LOCATION_MASTER_ID = LOCATION_MASTER.ID 
AND PR_EMPLOYEE_MASTER.PR_CATEGORY_MASTER_ID = PR_CATEGORY_MASTER.ID 
AND PR_EMPLOYEE_MASTER.PR_DESIG_MASTER_ID = PR_DESIG_MASTER.ID 
AND PR_EMPLOYEE_MASTER.COMPANY_MASTER_ID =  47
And PR_EMPLOYEE_MASTER.PR_DESIG_MASTER_ID = Pr_Desig_Mst_Prf_Det.Pr_Desig_Master_Id 
AND PR_JOB_PROFILE_MST.ID = PR_DESIG_MST_PRF_DET.PR_JOB_PROFILE_MST_ID

My last column is of JOB_PROFILE which has 3 rows with values as
Sales Planning & Order Fulfillment
Payments & Disbursements
Finance & Banking Marketing & Business Development

I want to merge into 1 row can anybody help me?

Posted 5-Jan-14 18:16pm

Member 9410081

Updated 6-Jan-14 6:15am

Jörgen Andersson

v3

Add a Solution

Comments

Maciej Los 6-Jan-14 16:08pm

We can't read from your screen and in your mind. You should be more specific and provide more details about your issue, instead posting above query.

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)

Jörgen Andersson · Accepted Answer · 2014-01-06T02:12:00

Solution 1

What version of Oracle are you having?
If it's 11.2 or later you can use Listagg[^]

BTW, using both ANSI joins and implicit joins in the same query is a bad idea, it's not supported by Oracle and even if it works now it might not after the next update.

Posted 6-Jan-14 2:12am

Jörgen Andersson

Comments

Maciej Los 6-Jan-14 16:10pm

+5!

Member 9410081 7-Jan-14 6:16am

Thanks.