locked
Cartesian product RRS feed

  • Question

  • 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 Toth Sunday, February 17, 2013 7:43 AM spelling
    Sunday, February 17, 2013 6:13 AM

Answers

All replies

  • 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 t1


    Best 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 6:44 AM
    Answerer
  • 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 8:45 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 9:15 AM
  • SELECT a.col1, b.col2, c.col3, d.col4, ....
    FROM   tbl a,
    CROSS JOJN tbl b
    CROSS JOIN tbl c
    CROSS JOIN tbl d

    Warning! If there are many rows and columns in the table, this will produced very many rows and your server may explode.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, February 17, 2013 3:32 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 #table2

    O/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


    Sunday, February 17, 2013 6:42 PM