Search code examples
oracleoracle12c

Convert child table rows as comma seperated values in Oracle

I have a requirement where i wanted the child table rows to be shown as comma seperated string instead of multiple rows.

Parent Table : Employee

Id Name
I1BF91860BCB711EDBF4EF0FBCBCA9E17 Tim
I1BF91860BCB711EDBF4EF0FBCBCA9E18 Sam
I1BF91860BCB711EDBF4EF0FBCBCA9E19 Rohan

Child Table: Trainings

Id Name Employee Id
I1BF91860BCB711EDBF4EF0FBCBCA9P10 Java Basics I1BF91860BCB711EDBF4EF0FBCBCA9E17
I1BF91860BCB711EDBF4EF0FBCBCA9P11 Angular I1BF91860BCB711EDBF4EF0FBCBCA9E17
I1BF91860BCB711EDBF4EF0FBCBCA9P12 DotNet I1BF91860BCB711EDBF4EF0FBCBCA9E18

Expected Output:

Id Name Trainings
I1BF91860BCB711EDBF4EF0FBCBCA9E17 Tim Java Basics, Angular
I1BF91860BCB711EDBF4EF0FBCBCA9E18 Sam DotNet
I1BF91860BCB711EDBF4EF0FBCBCA9E19 Rohan null

Query i have used

select id as Id, name as Name, (select listagg(tr.name, ',') within group(order by id) from trainings where employee_id = employee.id) as Trainings from employee where employee.id = trainings.employeeId(+)

Above is just a sample, but in realcase we have more number of columns and many tables being joined. I know we can use groupby clause, but in our case, we have more number columns(around 200) and all are dynamic where user can choose from Ui(Ag Grid).

Above query yields data with comma seperated string but i am getting duplicate data as shown below on joins.

May i know where am i going wrong? Is there any way to restrict duplicate values on joins and combine multiple child rows to single row using subqueries?

Id Name Trainings
I1BF91860BCB711EDBF4EF0FBCBCA9E17 Tim Java Basics, Angular
I1BF91860BCB711EDBF4EF0FBCBCA9E17 Tim Java Basics, Angular
I1BF91860BCB711EDBF4EF0FBCBCA9E18 Sam DotNet
I1BF91860BCB711EDBF4EF0FBCBCA9E19 Rohan null
Solution

  • If you don't need a correlated subquery, you can use a GROUP BY with LEFT JOIN

    select e."Id" as Id
, e."Name" as Name
, listagg(tr."Name", ',') within group(order by tr."Id") trainings
from employee e LEFT JOIN trainings tr
ON e."Id" = tr."Employee Id"
GROUP BY e."Id",e."Name"
    ID NAME TRAININGS
    I1BF91860BCB711EDBF4EF0FBCBCA9E17 Tim Java Basics,Angular
    I1BF91860BCB711EDBF4EF0FBCBCA9E18 Sam DotNet
    I1BF91860BCB711EDBF4EF0FBCBCA9E19 Rohan null

    fiddle

    Or as correlated subquery

    select e."Id" as Id
, e."Name" as Name
, (SELECT listagg(tr."Name", ',') within group(order by tr."Id") 
  FROM trainings tr WHERE e."Id" = tr."Employee Id")
  trainings
from employee e
    ID NAME TRAININGS
    I1BF91860BCB711EDBF4EF0FBCBCA9E17 Tim Java Basics,Angular
    I1BF91860BCB711EDBF4EF0FBCBCA9E18 Sam DotNet
    I1BF91860BCB711EDBF4EF0FBCBCA9E19 Rohan null

    fiddle