return all possible combinations of values in columns in SQL

Question

How do I return a list of all combinations of values in 4 columns so that they are new rows in Microsoft SQL Server 2012 e.g.

c1 c2 c3 c4
1  a  g  x
2  b  h  y

and turn it into

c1 c2 c3 c4
1  a  g  x
1  a  g  y
1  a  h  x
1  a  h  y

and so on having all (2X2X2X2)=16 rows in 4 columns

Similar solution for 2 columns has been answered on

Return all possible combinations of values on columns in SQL

I need the solution for 4 columns

Did you try anything?

Ric
– Ric

2013-09-10 10:51:31 +00:00
Commented Sep 10, 2013 at 10:51 — Ric
– Ric, Commented Sep 10, 2013 at 10:51

podiluska · Accepted Answer · 2013-09-10 10:52:11Z

5

With a cross join

select 
    t1.c1,
    t2.c2,
    t3.c3,
    t4.c4
from 
    yourtable t1 cross join
    yourtable t2 cross join
    yourtable t3 cross join
    yourtable t4

answered Sep 10, 2013 at 10:52

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zesar · Accepted Answer · 2013-09-11 13:10:07Z

2

As the link you provided had a good answer, modified below

select 
  distinct
  t1.C1,
  t2.C2,
  t3.C3,
  t4.C4
from 
  MyTable t1,
  MyTable t2,
  MyTable t3,
  MyTable t4

edited Sep 11, 2013 at 13:10

answered Sep 10, 2013 at 10:50

Zesar

5662 silver badges14 bronze badges

2 Comments

TToni Over a year ago

I think you want to make this "t3.C3" and "t4.C4". Using explicit cross join would be better for reading, so the point goes to podiluska anyway :-)

Zesar Over a year ago

Sorry for the obvious error here... nice catch! :-) As a comparison to the almost identical solution posted here i will keep the alternative

Collectives™ on Stack Overflow

return all possible combinations of values in columns in SQL

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related