How to combine multiple rows from 3 tables into one row

Question

I have 3 tables that look something like this:

Table A
Student id   | Course Name |Course Section
1                 | English            |A
1                 | Math                |B
2                 | Chemistry        |C
2                 | History             |A

Table B
Course Name |Course Section |Instructor ID |Percentage
Math               |B                       |67890           |50
Math               |B                       |54321           |50
History            |A                       |67890           |100
English           |A                        |12345          |100
Chemistry       |C                       |09876           |100

Table C
Instructor ID       |Instructor Email
09876                 | instrchem@testco.us
12345                 |instreng@testco.edu
54321                 |instrmathalso@testco.us
67890                 |instrhist@testco.edu

I want the output of my query to look like this:

StudentID   Course Name   Section   Instructor Email
1                 English              A            instreng@testco.edu
1                 Math                  B            instrhist@testco.edu;instrmath@testco.us
2                 Chemistry          C            instrchem@testco.us
2                 History               A            instrhist@testco.us

I have tried creating a table variable, using coalesce, and a couple of other suggestions, but I'm not having any success. Any ideas would be greatly appreciated.

Thank you.
eileen

Answer 1

You can try to join the three tables

SELECT      StudentID           = [TABLE A].[Student id]
            ,[TABLE A].[Course Name]
            ,Section            = [TABLE A].[Course Section]
            ,[TABLE C].[Instructor Email]
FROM        [TABLE A]
INNER JOIN  [TABLE B]
ON          [TABLE A].[Course Name]     = [TABLE B].[Course Name]
AND         [TABLE A].[Course Section]  = [TABLE B].[Course Section]
INNER JOIN  [TABLE C]
ON          [TABLE B].[Instructor ID]   = [TABLE C].[Instructor ID]

Answer 2

Try this

    WITH    cte
          AS ( SELECT   A.Student_Id AS StudentID
                      , A.CourseName AS [Course Name]
                      , A.CourseSection AS Section
               FROM     #TableA A
                        INNER JOIN #TableB B
                        ON B.CourseName = A.CourseName
                           AND B.CourseSection = A.CourseSection
                        INNER JOIN #TableC C
                        ON B.InstructorID = C.InstuctorID
             )
    SELECT  cte.StudentID
          , cte.[Course Name]
          , cte.Section
          , STUFF((SELECT   ',' + InstructorEmail
                   FROM     #TableC c1
                            INNER JOIN #TableB b1
                            ON b1.InstructorID = c1.InstuctorID
                            INNER JOIN #TableA a1
                            ON a1.CourseName = b1.CourseName
                   WHERE    a1.Student_Id = cte.StudentID
                            AND b1.CourseName = cte.[Course Name]
                            AND a1.CourseSection = cte.Section
            FOR   XML PATH('')
                    , TYPE).value('(./text())[1]', 'varchar(max)'), 1, 1, '') AS [Instructor Email]
    FROM    cte
    GROUP BY cte.StudentID
          , cte.[Course Name]
          , cte.Section;

Answer 3

You can do it using a scalar function and a cursor.

First create the tables with data:

CREATE TABLE dbo.Student
(
    StudentId int NOT NULL,
    CourseName nvarchar(20) NOT NULL,
    CourseSection nchar(1) NOT NULL
);

INSERT INTO dbo.Student (StudentId, CourseName, CourseSection)
    VALUES (1, N'English', N'A'),
        (1, N'Math', N'B'),
        (2, N'Chemistry', N'C'),
        (2, N'History', N'A');

CREATE TABLE dbo.Course
(
    CourseName nvarchar(20) NOT NULL,
    CourseSection nchar(1) NOT NULL,
    InstructorId int NOT NULL,
    [Percentage] int NOT NULL
);

INSERT INTO dbo.Course (CourseName, CourseSection, InstructorId, [Percentage])
    VALUES (N'Math', N'B', 67890, 50),
        (N'Math', N'B', 54321, 50),
        (N'History', N'A', 67890, 100),
        (N'English', N'A', 12345, 100),
        (N'Chemistry', N'C', 9876, 100);

CREATE TABLE dbo.Instructor
(
    InstructorId int NOT NULL,
    InstructorEmail nvarchar(50) NOT NULL
);

INSERT INTO dbo.Instructor (InstructorId, InstructorEmail)
    VALUES (09876, N'instrchem@testco.us'),
        (12345, N'instreng@testco.edu'),
        (54321, N'instrmathalso@testco.us'),
        (67890, N'instrhist@testco.edu');

Then create a scalar function which concatenates instructor emails. It uses a cursor to do this.

CREATE FUNCTION dbo.uf_ConcatEmails
(
    @CourseName nvarchar(20),
    @CourseSection nchar(1)
)
RETURNS nvarchar(500)
AS
BEGIN
    DECLARE @e nvarchar(500) = NULL;

    DECLARE myCursor CURSOR LOCAL
        FAST_FORWARD READ_ONLY
        FOR SELECT I.InstructorEmail
            FROM dbo.Course AS C
                INNER JOIN dbo.Instructor AS I
                    ON I.InstructorId = C.InstructorId
            WHERE C.CourseName = @CourseName AND C.CourseSection = @CourseSection
            ORDER BY I.InstructorEmail;

    OPEN myCursor;
    DECLARE @email nvarchar(50);
    FETCH NEXT FROM myCursor INTO @email;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @e = IIF(@e IS NULL, @email, CONCAT(@e, N';', @email));
        FETCH NEXT FROM myCursor INTO @email;
    END

    CLOSE myCursor;
    DEALLOCATE myCursor;

    RETURN @e;
END

Then do it!

SELECT StudentId, CourseName, CourseSection, dbo.uf_ConcatEmails(CourseName, CourseSection) AS 'InstructorEmail'
    FROM dbo.Student
    ORDER BY StudentId, CourseName;