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
4
  • 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, 2020 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
    – Dex
    Feb 19, 2020 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, 2020 at 19:33
  • The SQL even runs without error but does not make the distinction. I'll try another combination.
    – Dex
    Feb 19, 2020 at 19:41

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.