CODEWITHSUNDEEP
sqlsql-serverselect
epaezr
epaezr

Reputation: 474

How to combine multiple rows from three tables into one single string in SQL?

I have three tables that store different kinds of data according to an admission process.

The first table ([dbo].[Inscripciones_Pregrado]) stores the basic contact info of the future student. The second table ([dbo].[Checklist_Documentos]) stores the names and ID's for the documents that the student must have to complete the admission process. The third table ([dbo].[Checklist_Pregrado]) stores the documents that the student has effectively brought to the admissions office.

I need to combine this info so that we can export an excel file holding all the contact info, and the document's checklist for each student.

So, let's say table 1 haves 4 records: [dbo].[Inscripciones_Pregrado]

enter image description here

The second table ([dbo].[Checklist_Documentos]) haves eight records, different types of requested documents: enter image description here

And the third table ([dbo].[Checklist_Pregrado]) haves 16 records:

enter image description here

What I need is that this three tables get combined according to the aspirant's ID:

enter image description here

So far, I've tried to different types of codes:

Code 1

SELECT
    [dbo].[Inscripciones_Pregrado].[ID],
    [dbo].[Inscripciones_Pregrado].[Name],
    [dbo].[Inscripciones_Pregrado].[ProgramID],
    [dbo].[Checklist_Documentos].[Document] 
FROM [dbo].[Inscripciones_Pregrado] 

INNER JOIN
    [dbo].[Checklist_Pregrado]
    ON [dbo].[Checklist_Pregrado].[IdForm] = [dbo].[Inscripciones_Pregrado].[ID]

INNER JOIN
    [dbo].[Checklist_Documentos]
    ON [dbo].[Checklist_Documentos].[ID] = [dbo].[Checklist_Pregrado].[IdDoc]

This one gets one line per each document, so the name and other records are repeated according to the number of documents that the aspirant has on his folder.

Code 2

SELECT
    [dbo].[Inscripciones_Pregrado].[ID],
    [dbo].[Inscripciones_Pregrado].[Nombres],
    [dbo].[Inscripciones_Pregrado].[Apellido1],
    (STUFF((SELECT CAST(', ' + [dbo].[Checklist_Pregrado].[IdDocumento] AS varchar(max))
        FROM [dbo].[Checklist_Pregrado]
        WHERE([dbo].[Inscripciones_Pregrado].[ID] = [dbo].[Checklist_Pregrado].[IdForm])
        FOR XML PATH ('')), 1, 2, '')) AS [Docs]
FROM [dbo].[Inscripciones_Pregrado]

This code get's and error: Conversion failed when converting the varchar value ', ' to data type int.

I appreciate all your help and comments.

Thanks

Upvotes: 1

Views: 834

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

For your Code 2, you can fix the error by doing the CAST before you do the concatenation.

In other words:

(STUFF((SELECT ', ' + CAST([dbo].[Checklist_Pregrado].[IdDocumento] AS varchar(max))

Upvotes: 2

epaezr
epaezr

Reputation: 474

@Tab Alleman gave me the hint on the answer, I haven't noticed that error in the writing. It solved the issue, but the code would only bring the document's ID from the second table ([dbo].[Checklist_Documentos]) So I added an INNER JOIN to combine the data:

SELECT
    [dbo].[Inscripciones_Pregrado].[ID],
    [dbo].[Inscripciones_Pregrado].[Nombres],
    [dbo].[Inscripciones_Pregrado].[Apellido1],
    (STUFF((SELECT ', ' + CAST([dbo].[Checklist_Documentos].[Documento] AS varchar(max))
        FROM [dbo].[Checklist_Pregrado]
        INNER JOIN
            [dbo].[Checklist_Documentos] 
            ON [dbo].[Checklist_Documentos].[ID] = [dbo].[Checklist_Pregrado].[IdDocumento] 
        WHERE([dbo].[Inscripciones_Pregrado].[ID] = [dbo].[Checklist_Pregrado].[IdForm])
        FOR XML PATH ('')), 1, 2, '')) AS [Docs]
FROM [dbo].[Inscripciones_Pregrado]

Upvotes: 2

Related Questions