Create a Cartesian product of all records of a table using a set of predefined values

Suppose we have a table like below in SQL Server:

CREATE TABLE dbo.mytable
(
    Name VARCHAR(20),
    Val INT
)

and we populate it using:

INSERT INTO dbo.mytable
VALUES
('A', 10),
('B', 5)
('C', 12)

We now want to create a Cartesian product of those records using a small set of date values

Date
2017-01-01
2017-02-01
2017-03-01

Using the above set of date values the required output is:

Name	Val	Date
A	10	2017-01-01
A	10	2017-02-01
A	10	2017-03-01
B	5	2017-01-01
B	5	2017-02-01
B	5	2017-03-01
C	12	2017-01-01
C	12	2017-02-01
C	12	2017-03-01

The first step in solving our problem is to create an in-line table containing the date values making use of a Table Value Constructor.

To create the Cartesian product we can use a CROSS JOIN that does exactly this:

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

So, our query looks like this:

SELECT  Name, Val, x.myDate
FROM mytable
CROSS JOIN (VALUES ('2017-01-01'), 
                   ('2017-02-01'), 
                   ('2017-03-01')) x(myDate)
ORDER BY Name, myDate

Click here for a live demo.