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.
## No comments:

## Post a Comment