Monday, September 1, 2014

Dear Friends,

Here I would like to share the query (Oracle DB) to fetch list of columns(comma separated) mapped to single column. i.e one column associated with multiple columns.
In below scenario, employee belongs to multiple departments.


COLUMN departments FORMAT A50

SELECT  emp.empemail,emp.empmobile, wm_concat(dept.empdeptno) AS departments
FROM   fos_empomer_dept dept inner join fos_empomer emp on dept.empid = emp.empid
and dept.deptActive =1
GROUP BY emp.empemail,emp.empmobile;   

SELECT dept.empid,emp.empemail,emp.empmobile, wm_concat(dept.empdeptno) AS departments
FROM   fos_empomer_dept dept inner join fos_empomer emp on dept.empid = emp.empid
and dept.deptActive =1
GROUP BY dept.empID,emp.empemail,emp.empmobile;   



The out put will be as below

+-----------------+------------------------------------+-----------------------+---------------------------------------+
   EmpID            EmpEmail                       EmpMobile            Departments
 +-----------------+-----------------------------------+-----------------------+---------------------------------------+
       101             raghu@fewa.gov.ae              1234                   sales,purchase,audit
+-----------------+------------------------------------+-----------------------+---------------------------------------+



Happy Coding!

Thank you,
Raghuram Reddy Gottimukkula
Adobe Certified Professional in Advanced ColdFusion
Dubai, UAE