CODEWITHSUNDEEP
sql-server
user2764527
user2764527

Reputation: 21

return all possible combinations of values in columns in SQL

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

Upvotes: 2

Views: 5917

Answers (2)

Zesar
Zesar

Reputation: 566

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

Upvotes: 2

podiluska
podiluska

Reputation: 51514

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

Upvotes: 5

Related Questions