CODEWITHSUNDEEP
sql-serverdatabasecross-join
jayz
jayz

Reputation: 401

SQL Server Cross Join

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.

Upvotes: 0

Views: 330

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 3

Related Questions