CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SDReyes
SDReyes

Reputation: 9954

Cross Join 'n' times a table

It is possible to write a generic function/procedure/select/somethingElse to cross-join a table against himself 'n' times? (yes, 'n' is a given parameter : )

How would you do it?

Example

Having this table:

 Value
-------
   1
   2
   3

cross join it 2 times, would return:

 Value  | Value
------------------
    1       1
    1       2
    1       3
    2       1
    2       2
    2       3
    3       1
    3       2
    3       3

Upvotes: 0

Views: 2121

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332691

Using dynamic SQL, SQL Server 2005+ (@table_name and @numCrossJoins are stored procedure parameters):

DECLARE @upperLimit INT
    SET @upperLimit = 1

DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT * FROM '+ @table_name +' '

BEGIN 

  WHILE (upperLimit <= @numCrossJoins)
  BEGIN

    SET @SQL = @SQL + 'CROSS JOIN '+ QUOTENAME(@table_name) +' '

    SET @upperLimit = @upperLimit + 1
  END

  EXEC sp_executesql @SQL

END

Upvotes: 5

Michael Buen
Michael Buen

Reputation: 39433

Try this:

SET @SQL = 'SELECT * FROM ' + replicate('[' + @table_name + '],', @N);

set @SQL = LEFT(LEN(@SQL) - 1);

EXEC sp_executesql @SQL;

Upvotes: 2

A-K
A-K

Reputation: 17090

If you need to come up with all possible permutations, here is an example:

All Permutations For A String

Upvotes: 1

Paul Kearney - pk
Paul Kearney - pk

Reputation: 5543

You can generate dynamic sql to output as many cross joins as you need:

create table #t (value int)

insert into #t values (1)
insert into #t values (2)
insert into #t values (3)

declare @n int
set @n = 4

declare @sql varchar(max)
set @sql = 'SELECT * FROM #t t'
declare @i int
set @i = 0
while (@i <= @n)
begin
    set @sql = @sql + ' cross join #t t' + CAST(@i as varchar)
    set @i = @i + 1
end

print @sql
execute(@sql)

drop table #t

Upvotes: 3

Related Questions