Concatenation of Records via FOR XML PATH - SQL SERVER

Question

Concatenation of Records via FOR XML PATH - SQL SERVER

Navigation

#1 by (2 votes)

2

Good afternoon dear, I have the following problem:

I'm developing a Query where I need to check which documents have been delivered by the student, these documents that are in the pre_matricula_doc table and have the following structure:

Thenthepre-enrollmentstudent2presenteddocuments1and2,andthepre-enrollmentstudent3presentedthedocument3.

Ofthedocumentsthatwerepresentedbythestudent"2" the 1 corresponds to the CPF, the 2 to the RG, of the student "3" it is the birth certificate.

I'm bringing these documents with the following query:

SELECT      '; ' + DA.DESCRICAO
FROM        BD.pre_matricula_doc PDA
LEFT JOIN   BD.DOCUMENTOS DA ON PDA.pre_matricula   IN (1, 2)
                            AND DA.CODIGO           = PDA.documento 
                            AND PDA.apresentado     = '1'       
GROUP BY    PDA.pre_matricula
        ,   DA.descricao

The table PDA is the table where the documents of the pre-registration (which I placed the above structure) and the table DA is where is the description of the documents (CPF, RG, Birth Certificate).

It happens that when I bring this information to only one student the query works correctly, however, when I mention more than one code in CLAUSULA IN the query concatenates all the documents and brings back all the students, see:

Aluno     Documentos apresentados

-----------------------------------

2         RG; CPF; Certidão de nascimento
3         RG; CPF; Certidão de nascimento

When the desired result would be:

Aluno     Documentos apresentados

-----------------------------------

2         RG; CPF;
3         Certidão de nascimento;

What can I do to bring the right information?

Thank you in advance.

sql xml sql-server-2014

asked by anonymous 11.12.2018 / 19:27

1 answer

Receiving object by parameter How to create a shell script that puts an echo at the beginning of each line in a file

score 2 · Accepted Answer

In your query you are placing filters directly on LEFT JOIN when they should be in the WHERE clause:

SELECT      '; ' + DA.DESCRICAO
FROM        BD.pre_matricula_doc    PDA
LEFT JOIN   BD.DOCUMENTOS           DA  ON DA.CODIGO = PDA.documento
WHERE       PDA.pre_matricula   IN (2, 3)
        AND PDA.apresentado     = '1'
GROUP BY    PDA.pre_matricula
        ,   DA.descricao

With the filter PDA.pre_matricula IN (1, 2) will only bring those with value 1 or 2 of column pre_matricula (typing error?), from there you have changed to IN (2, 3) , where you can get information for values 2 and 3.