SQL returns two records to a field, how to join them?

Question

I have the following SQL code in DB2:

SELECT p.nome_pessoa, a.dt_nascimento, alc.matricula, d.numero_documento, d.id_tdoc_pessoa FROM pessoas as p
INNER JOIN alunos as a ON p.id_pessoa = a.id_pessoa
INNER JOIN acad_alunos_cursos as alc ON alc.id_aluno = a.id_aluno
INNER JOIN doc_pessoas as d ON p.id_pessoa = d.id_pessoa WHERE p.nome_pessoa = 'Vitor Lucas Pires Cordovil'

After executing the query I have the following return:

NAME - DATE OF BIRTH - REGISTRATION - DOCUMENT NUMBER - DOCUMENT TYPE

Vitor Lucas Pires Cordovil - 1994-02-27 - 2014300028 - 951.249.865-X3 - 1

Vitor Lucas Pires Cordovil - 1994-02-27 - 2012430028 - 10491872 - 3

It returns me the two records, because there are two documents registered to the person, when the document type is 1 then it is a RG, when it is 3 is a CPF. Only I want the query to return me only one record with two columns: RG and CPF, all in the same row. How to do?

Answer 1

I would do something like this:

SELECT p.nome_pessoa, a.dt_nascimento, alc.matricula, df.numero_documento as RG, dj.numero_documento as CPF, df.id_tdoc_pessoa as df, dj.id_tdoc_pessoa as dj FROM pessoas as p
INNER JOIN alunos as a ON p.id_pessoa = a.id_pessoa
INNER JOIN acad_alunos_cursos as alc ON alc.id_aluno = a.id_aluno
LEFT JOIN doc_pessoas as df ON p.id_pessoa = df.id_pessoa and df.id_tdoc_pessoa = 1
LEFT JOIN doc_pessoas as dj ON p.id_pessoa = dj.id_pessoa and dj.id_tdoc_pessoa = 3
WHERE p.nome_pessoa = 'Vitor Lucas Pires Cordovil'

That would give a new JOIN in the table doc_people, restricting for each specific type, in your case 1 and 3. I used LEFT instead of INNER so that the line is displayed even if there is no value for type = 1 or 3.