Creating multiple new rows using data from other rows

Question

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

Class	Teacher	Subject
1	Adam	English
2	Bill	Maths
3	Carl	Maths
4	Dave	Science
5	Evan	Maths

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:

Class	Teacher	Subject
1	Adam	English
2	Bill	Maths
2	Carl	Maths
2	Evan	Maths
3	Bill	Maths
3	Carl	Maths
3	Evan	Maths
4	Dave	Science
5	Bill	Maths
5	Carl	Maths
5	Evan	Maths

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

score 2 · Answer 1 · answered Oct 04 '21 at 15:09

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

Class	Teacher	Subject
1	Adam	English
2	Bill	Maths
2	Carl	Maths
2	Evan	Maths
3	Bill	Maths
3	Carl	Maths
3	Evan	Maths
4	Dave	Science
5	Bill	Maths
5	Carl	Maths
5	Evan	Maths

score 0 · Answer 2 · answered Oct 04 '21 at 15:21

0

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

answered Oct 04 '21 at 15:21

Somendra Kanaujia

727
5
12

This script does not do what is asked for in the OP? They want to assign all teachers of each subject to all classes of that subject. You script is `Maths` specific. – iamdave Oct 04 '21 at 15:51
in question it is mention that **What I need to do is create a table that takes all maths teachers, and associates them with all maths classes** .- @iamdave – Somendra Kanaujia Oct 05 '21 at 05:37

Creating multiple new rows using data from other rows

2 Answers2

Query

Result