Repeat values in SQL query

Question

I have data in a table that looks like this

TeacherNo  TeacherName
1          Smith
2          Doe

I need to run a query that will produce output that looks something like this. Note the ProductID values below would just be hardcoded values in my script.

TeacherNo  TeacherName  ProductID
1          Smith        ABC
1          Smith        DEF
1          Smith        GHI
2          Doe          ABC
2          Doe          DEF
2          Doe          GHI

I know I could do it by repeating my sql statement and doing UNION ALL, however I didn't want to repeat my code so many times (in reality these may be repeated up to 6 times each)

Thank you.

Answer 1

Try this

SELECT *
FROM   YOURTABLE
       CROSS JOIN (VALUES ('ABC'),
                          ('DEF'),
                          ('GHI')) tc(product_id) 

SQL FIDDLE DEMO

Answer 2

You can do like below

   Create table #dummytbl(ProductId nvarchar(50));
   INSERT INTO #dummytbl VALUES ('ABC');
   INSERT INTO #dummytbl VALUES ('DEF');
   INSERT INTO #dummytbl VALUES ('GHI');
   select i.TeacherNo, i.TeacherName, d.ProductId  from Teacher i
          inner join #dummytbl
          order by i.TeacherNo;