Cartesian product
-
Sunday, February 17, 2013 6:13 AM
Hi All,
I have a table with a bunh of columns. I need to write a query to pull all the possible combinations(cartesian product) of columns Column1, Column2, Column3. Any thoughts on how to do this? I tried self join without any luck. Thank in advance.
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Sunday, February 17, 2013 7:43 AM spelling
All Replies
-
Sunday, February 17, 2013 6:44 AMAnswerer
Can you show the table's structure and an expected result? http://msdn.microsoft.com/en-us/library/ms190690(v=sql.105).aspx
create table #t (c1 int, c2 int,c3 int)
insert into #t values (1,2,3)
insert into #t values (4,5,6)
select * from #t cross join #t t1Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
- Marked As Answer by SnowShine429 Monday, February 18, 2013 3:23 PM
-
Sunday, February 17, 2013 7:46 AMModerator
As suggested, try CROSS JOIN. BOL:
USE AdventureWorks2008R2;
GO
SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
ORDER BY p.BusinessEntityID;http://msdn.microsoft.com/en-us/library/ms190690(v=sql.105).aspx
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012 -
Sunday, February 17, 2013 8:45 AM
Yes as stated above Cross Join Works the best if the requirement is all the possible combinations within the same table or different tables because
your joining everything as in the syntax of Cross Join there is no ON Clause that puts the limitation of joining 2 columns based on a Match .
-
Sunday, February 17, 2013 9:15 AM
See these
http://www.fluffycat.com/SQL/Cartesian-Joins/
http://msdn.microsoft.com/en-us/library/ms190690%28v=SQL.105%29.aspx
Many Thanks & Best Regards, Hua Min
-
Sunday, February 17, 2013 3:32 PM
-
Sunday, February 17, 2013 6:42 PM
Hi,
I am giving you the required script with sample tables as well i have shown the output. so cross verify that you want like below only or something else
SQL Code:
Create table #table1 (a1 int, a2 int,a3 int)
Create table #table2 (b1 int, b2 int,b3 int)
insert into #table1 values (1,2,3)
insert into #table1 values (11,22,33)
insert into #table1 values (21,22,23)
insert into #table1 values (31,32,33)
insert into #table2 values (54,55,56)
insert into #table2 values (64,65,66)
insert into #table2 values (74,75,76)
select * from #table1, #table2
Drop table #table1
Drop table #table2O/P
----------------a1
a2
a3
b1
b2
b3
1
2
3
54
55
56
11
22
33
54
55
56
21
22
23
54
55
56
31
32
33
54
55
56
1
2
3
64
65
66
11
22
33
64
65
66
21
22
23
64
65
66
31
32
33
64
65
66
1
2
3
74
75
76
11
22
33
74
75
76
21
22
23
74
75
76
31
32
33
74
75
76
Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com
- Edited by Rahul Kumar (Rahul Vairagi) Sunday, February 17, 2013 6:45 PM format
.png)
