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 |
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 |
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 |