Is there a way to add extra rows to a table, pulling the data from other rows? Specifically, I am needing to relate all teachers of a specific subject to all classes. Currently, the code looks like this:

SELECT 
    class
    teacher
    subject
FROM TeachingSets

and produces a table like this

What I need to do is create a table that takes all maths teachers, and associates them with all maths classes, so that the new table looks like this:

I have tried this:

SELECT 
    class
    CASE 
        WHEN subject = 'Maths' 
        THEN 
            (SELECT DISTINCT
                teacher
            FROM TeachingSets
            WHERE subject = 'Maths')
        ELSE teacher
    END AS teacher,
    subject
FROM TeachingSets

but it throws up an error as the subquery returns more than one value.

Any help would be greatly appreciated

Thank you

CodePudding user response：

This is fairly straightforward join logic:

Query

declare @t table(Class int,Teacher varchar(10),Subject varchar(10));
insert into @t values
 (1,'Adam','English')
,(2,'Bill','Maths')
,(3,'Carl','Maths')
,(4,'Dave','Science')
,(5,'Evan','Maths')
;

select c.Class
      ,s.Teacher
      ,s.Subject
from @t as c
    join @t as s
        on c.Subject = s.Subject
order by c.Class
         ,s.Teacher;

Result

CodePudding user response：

Use below code

SELECT TBL.CLASS,ISNULL(X.TEACHER,TBL.TEACHER) TEACHER,TBL.SUBJECTS FROM 
TeachingSets as TBL LEFT JOIN (
SELECT * FROM TeachingSets WHERE SUBJECTS='MATHS'
)X ON  TBL.SUBJECTS=X.SUBJECTS