I have a query as follows:
select c.case_id, c.last_name, c.first_name, lkp_alt.descr alt, lkp_cs.descr caseStatus, u.last_name || ','|| u.first_name createdBy,
ul.last_name ||','||ul.first_name lastModifiedBy, c.LAST_MODIFIED_DATE
from cases c left outer join lkP_alt_show_cause_authority lkp_alt
on c.ATL_SHOW_CAUSE_AUTHORITY = lkp_alt.id
left outer join cases_case_status cStatus
on c.case_id = cStatus.case_id
left outer join lkp_case_status lkp_cs
on cStatus.case_status_id = lkp_cs.id
left outer join users u
on c.created_by = u.id
left outer join users ul
on c.LAST_MODIFIED_BY = ul.id;
Output looks like below,
case_id last_name first_name alt caseStatus createdBy lastModifiedBy
1 lName fName MCCDC Active aaa aaa
1 lName fName MCCDC Closed aaa aaa
100 lName1 fName1 MCCDC Closed aaa ,
100 lName1 fName1 MCCDC Active aaa ,
16023 lName2 fName2 MCCDC Closed aaa ,
16023 lName2 fName2 MCCDC Active aaa ,
16025 lName3 fName3 MCCDC Active aaa ,
16025 lName3 fName3 MCCDC Closed aaa ,
16027 lName4 fName4 II MEF JPAS aaa ,
16027 lName4 fName4 II MEF IGMC aaa ,
16027 lName4 fName4 II MEF Active aaa ,
16029 lName5 fName5 MCCDC Closed aaa ,
16029 lName5 fName5 MCCDC Active aaa ,
16031 lName6 fName6 MCCDC Closed aaa ,
16031 lName6 fName6 MCCDC Active aaa ,
From above caseStatus for each caseId has a ManytoOne relationship. I want the display as follows where caseStatus is seperated by commas if they are more than 1.
case_id last_name first_name alt caseStatus createdBy lastModifiedBy
1 lName fName MCCDC Active, Closed aaa aaa
100 lName1 fName1 MCCDC Active, Closed aaa ,
16023 lName2 fName2 MCCDC Active, Closed aaa ,
16025 lName3 fName3 MCCDC Active, Closed aaa ,
16027 lName4 fName4 II MEF JPAS, IGMC, Active aaa ,
16029 lName5 fName5 MCCDC Active, Closed aaa ,
16031 lName6 fName6 MCCDC Active, Closed aaa ,
