0

I'm using the oracle-xe 11g version and I have the below sql code that I aggregation the course code, but the output has duplicates.

SELECT
    b.alu_cpf            AS login,
    b.alu_nomeco         AS nome_completo,
    b.alu_emailpessoal   AS email,
    LISTAGG(d.cur1codigo, ', ') WITHIN GROUP (ORDER BY bdetcom.somentenumerosalfa(d.cur1codigo)) AS cod_curso,
    LISTAGG(d.cur1nomeco, ', ') WITHIN GROUP (ORDER BY bdetcom.somentenumerosalfa(d.cur1nomeco)) AS nome_curso,   
    TO_CHAR(b.alu_sexo, '9') AS sexo,
    a.alu_codigo AS matricula,
    b.alu_dtnascimento,  AS data_nascimento
FROM
    matricula_curr_dis              a
    INNER JOIN aluno                b ON b.alu_codigo = a.alu_codigo
    INNER JOIN dis01                c ON a.dis1codigo = c.dis1codigo
    INNER JOIN CUR01                d ON a.cur1codigo = d.cur1codigo
GROUP BY
    b.alu_cpf,
    b.alu_nomeco,
    b.alu_emailpessoal,
    a.alu_codigo,
    b.alu_sexo,
    b.alu_dtnascimento
ORDER BY
    nome_completo

Data:

login | nome | email  |      cod_curso
12345  fulano  xxxx     cod1, cod1, cod1, cod2

How I can distinct the cod_curso so it can look like this?

login | nome | email  |  cod_curso
12345  fulano  xxxx     cod1, cod2
  • 1
    You need to do it with a select that only gets the distinct values before you do the listagg. Newer versions of oracle have the feature built-in. – Joe W Feb 19 at 17:17
  • @JoeW, maybe this way: INNER JOIN (SELECT DISTINCT dis01.dis1codigo FROM dis01) c ON a.dis1codigo = c.dis1codigo INNER JOIN (SELECT DISTINCT cur01.cur1codigo, cur01.cur1nomeco FROM cur01) d ON a.cur1codigo = d.cur1codigo – Dexter77 Feb 19 at 19:11
  • Something like that should work. I didn't have time to write something up but was hoping hint could help. – Joe W Feb 19 at 19:33
  • The SQL even runs without error but does not make the distinction. I'll try another combination. – Dexter77 Feb 19 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.