CODEWITHSUNDEEP
t-sql
Chris
Chris

Reputation: 3712

Simplified cross joins?

Let's sat I have a Table 'A' with rows:

A
B
C
D

Is there a simple way to do a cross join that creates

A  1
A  2
A  3
A  4
...
D  1
D  2
D  3
D  4

without creating a second table?

Something like:

SELECT *
FROM A
  CROSS JOIN (1,2,3,4)

Upvotes: 4

Views: 496

Answers (3)

Philip Kelley
Philip Kelley

Reputation: 40359

The following would work for a table of any size (though I only tested it against 6 rows). It uses the ranking functions available in SQL Server 2005 and up, but the idea should be adaptible to any RDBMS.

SELECT ta.SomeColumn, cj.Ranking
 from TableA ta
  cross join (select row_number() over (order by SomeColumn) Ranking from TableA) cj
 order by ta.SomeColumn, cj.Ranking

Upvotes: 1

Will
Will

Reputation: 2978

something like that should work, i guess

select * from A cross join (select 1 union all select 2 union all select 3 union all select 4) as tmp

you will create a second table, but you won't persist it.

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30815

You should be able to achieve this via

select * from A cross join
(select 1
  union all
 select 2
  union all
 select 3
  union all
 select 4)

Upvotes: 0

Related Questions