CODEWITHSUNDEEP
sqloracle-database
Dummy
Dummy

Reputation: 29

SQL ROWS CONCAT + JOIN

Hello I'm using Oracle SQL and I want to eliminate the duplicate rows (with the same teacher). I know is a simple task but I am kind of stuck... Please help me.

SELECT a.name||' '|| a.first_name AS teacher_name,
       c.course_title AS courses 
FROM teacher a JOIN
     didactic b
     ON a.id_prof = b.id_prof JOIN
     courses c 
     ON b.id_course = c.id_cours 
ORDER BY teacher_name;

This is the output:

teacher_name    courses
A                sport
A                math
B                one
B                two

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You are looking for listagg():

SELECT a.name || ' ' || a.first_name AS teacher_name,
       LISTAGG(c.course_title, ',') WITHIN GROUP (ORDER BY c.course_title) AS courses 
FROM teacher a JOIN
     didactic b
     ON a.id_prof = b.id_prof JOIN
     courses c 
     ON b.id_course = c.id_cours
GROUP BY a.name || ' ' || a.first_name
ORDER BY teacher_name;

Upvotes: 1

Related Questions