How to prevent Varchar Appending Duplicate Information

Question

I'm learning PL/SQL stored functions in Oracle SQL Developer and I'm having trouble trying to create a function that returns a row that does not contain duplicate information.

Schema information:

1. APPLICANT which has an ANUMBER (applicant number)
2. SPOSSESSED (skill possessed) which has an SNAME (skill name) and ANUMBER (to link applicants to skills)
3. POSITION which has a PNUMBER
4. SNEEDED which has SNAME and PNUMBER to link required skills to a position

The task essentially is to have a function that takes an anumber and returns a string with the positions available that the applicant has the skills for.

My current code:

CREATE OR REPLACE FUNCTION applicant_position_titles(anum NUMBER) RETURN VARCHAR IS
course_list VARCHAR(300);
first_position POSITION.TITLE%TYPE;
current_position POSITION.TITLE%TYPE;

BEGIN
    course_list := '';

FOR spossessed_cursor IN (SELECT sname FROM SPOSSESSED WHERE anumber = anum)
LOOP
    FOR sneeded_cursor IN (SELECT PNUMBER FROM SNEEDED WHERE spossessed_cursor.sname = sname)
    LOOP
        FOR position_cursor IN (SELECT TITLE FROM POSITION WHERE sneeded_cursor.PNUMBER = PNUMBER)         
        LOOP
            course_list := course_list || ' ' || position_cursor.title;
        END LOOP;
    END LOOP;
END LOOP;

RETURN course_list;
END applicant_position_titles;
/

My Select Statement:

SELECT anumber, applicant_position_titles(anumber) 
FROM APPLICANT 
WHERE applicant_position_titles(anumber) IS NOT NULL;

The results:

ANUMBER	APPLICANT_POSITION_TITLES(ANUMBER)
1	lecturer lecturer lecturer senior lecturer lecturer professor professor professor

I would like to know how I can optimise this code function to prevent from duplicating positions.

For example for the first row I would like column 2 to have:

ANUMBER	APPLICANT_POSITION_TITLES(ANUMBER)
1	lecture senior lecturer professor

I know that it is happening because each skill can be applied to multiple positions but I don't know what the best way of fixing this issue would be. I've tried a few things such as storing and comparing VARCHARS but nothing seems to be working.

I'm still learning SQL, please go easy on my disgusting code. Thankyou :)

Answer 1

@OldProgrammer is right u can do it in one select statement. Here is my sample table and data:

create table SPOSSESSED (sname varchar2(30), anumber number);
create table sneeded (sname varchar2(30), pnumber number);
create table "position" (title varchar2(30), pnumber number);
-------------------------------------
insert into SPOSSESSED values('name',1);
insert into SPOSSESSED values('name2',1);
insert into SPOSSESSED values('name3',1);
--------------------------------------
insert into sneeded values ('name',111);
insert into sneeded values ('name2',222);
insert into sneeded values ('name3',222);
--------------------------------------------
insert into "position" values ('lecturer',111);
insert into "position" values ('professor',222);

And here is that one select statement:

select sp.anumber, LISTAGG(p.title,' ') WITHIN GROUP (ORDER BY sp.anumber) AS title
from spossessed sp,sneeded sn,"position" p
where
sp.sname=sn.sname and
p.pnumber=sn.pnumber
group by sp.anumber

Result:

ANUMBER | TITLE
1        lecturer professor

Edit that removes same position title:

select anumber, LISTAGG(title,' ')
WITHIN GROUP (ORDER BY anumber) AS TITLE
from (
        select distinct sp.anumber, p.title
        from spossessed sp,sneeded sn,"position" p
        where sp.sname=sn.sname and p.pnumber=sn.pnumber
      )
group by anumber;