SQL Server Cross Join

Question

I am using a CROSS JOIN function to create a view. The code that I have found is like below.

SELECT tbl.*
  ,y.GOAL_VERSION_NO
FROM EVALGOAL_GROUP_EMP AS tbl
CROSS JOIN (SELECT TOP (SELECT MAX(GOAL_VERSION_NO) FROM GOAL) * FROM(VALUES(1),(2),(3),(4),(5) /*add the max count here*/) AS x(GOAL_VERSION_NO)) AS y

In this code sample, the output of SELECT MAX(GOAL_VERSION_NO) FROM GOAL can be any value, per say it can be 1-1000. But I learned only to insert the CROSS JOIN values as VALUES (1),(2),(3) and so on as I did in my code. Is there any way I can enter a unlimited amount of cross join values here(By unlimited I mean maximum of 1000 and it's difficult to hard code like above and sometime it can be more than 1000).
Please help me. Thank you.

use a `NUMBER TABLE`. Create a number table with max value that you required — Squirrel, Apr 26 '16 at 04:50

score 3 · Answer 1 · edited May 23 '17 at 12:16

3

You can use a Tally Table to generate sequence of numbers up to a desired value:

DECLARE @Range AS INT = 1000;

WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
CteTally(GOAL_VERSION_NO) AS(
    SELECT TOP(@Range) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
SELECT
    tbl.*,
    y.GOAL_VERSION_NO
FROM EVALGOAL_GROUP_EMP AS tbl
CROSS JOIN CteTally y

Simplified explanation of Tally Table

edited May 23 '17 at 12:16

Community

1
1

answered Apr 26 '16 at 04:50

Felix Pamittan

31,544
7
41
67

@jayz, please read the referenced article before using this code in production. – Felix Pamittan Apr 26 '16 at 05:16
@jayz, [Please feel free to accept my answer if you feel it was useful to you](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Felix Pamittan Apr 26 '16 at 05:59

SQL Server Cross Join

1 Answers1