epaezr epaezr - 4 months ago 5
SQL Question

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

Answer

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))
Comments