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
('A', 10),
('B', 5)
('C', 12)

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


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-03-01')) x(myDate)
ORDER BY Name, myDate
Click here for a live demo.

No comments:

Post a Comment

Featured Post

Store the result of a select query into an array variable

Popular Posts