Insert duplicate rows for subjects-CodePudding

Is there anyway to insert duplicate rows in a query without altering the existing table (two rows for all subjects except for foreign language, which requires three rows).

select * from school_data

student_name            subjects                class_date
  Joe                    Math                    10/1/2021
  Mike                  Science                  10/2/2021
  Eddie                 History                  10/3/2021
  Steve                 English                  10/4/2021
  John                 Foreign Language          10/5/2021

Requested:

student_name            subjects                class_date
  Joe                    Math                    10/1/2021
  Joe                    Math                    10/1/2021
  Mike                  Science                  10/2/2021
  Mike                  Science                  10/2/2021
  Eddie                 History                  10/3/2021
  Eddie                 History                  10/3/2021
  Steve                 English                  10/4/2021
  Steve                 English                  10/4/2021
  John                 Foreign Language          10/5/2021
  John                 Foreign Language          10/5/2021
  John                 Foreign Language          10/5/2021

CodePudding user response：

JOIN with a generated table containg 3 rows

select * 
from school_data
join generate_series(1,3) t(n) on t.n<=2 or subjects ='Foreign Language'
order by student_name

CodePudding user response：

Well, you could create another table called "subjects", and input the subject name as many times as you would like (such as two for Math and three for Foreign Language). This will create a one-to-many result when joining to it. Just need to make sure you have all the subjects in there.

Subject Table Contents:

Math
Math 
Science 
Science 
Foreign Language
Foreign Language
Foreign Language

Then a query may look something like this:

    select sc.student_name, sc.subject_name, sc.class_date
    from student_classes sc
    join subjects s
      on s.subject_name = sc.subject_name
    order by 1

Db-fiddle found here: https://www.db-fiddle.com/f/bYxyZidB2cPPBj3w8Pxj9H/0

EDIT: If you cannot create a table, then you could use a CTE. I'm sure there are prettier solutions out there, but this works.

    with subjects as
        (
        select 'Math' as subject_name
        union all
        select 'Math' as subject_name
        union all
        select 'Science' as subject_name
        union all
        select 'Science' as subject_name
        union all
        select 'Foreign Language' as subject_name
        union all
        select 'Foreign Language' as subject_name
        union all 
        select 'Foreign Language' as subject_name
        )
    select sc.student_name, sc.subject_name, sc.class_date
    from student_classes sc
    join subjects s
      on s.subject_name = sc.subject_name
    order by 1

Db-fiddle found here: https://www.db-fiddle.com/f/o4vxshi9ua3rLCNdT9oXAR/0