CODEWITHSUNDEEP
t-sqlself-joincross-join
Lanceleazol
Lanceleazol

Reputation: 53

How best can I produce a cartesian product with the different columns of a same table?

Suppose I do have a table ManjoloHigh that holds the following columns: Firstname, Surname, Age, Gender, Village, and you're required to produce a cartesian product based on the following these two columns: Village, Firstname.

What is the best way to produce a cartesian product in T-SQL?

Upvotes: 0

Views: 967

Answers (3)

Menno
Menno

Reputation: 12641

Assuming that you are trying to retreive a set of unique possible combinations of the values in given columns.

You could either explode your resultset by creating a huge cartensian product, followed by DISTINCT, like the following:

SELECT DISTINCT V.Village, F.Firstname 
FROM MANJOLOHIGH AS V 
CROSS JOIN MANJOLOHIGH AS F

Or you could create subsets of the unique values in every column and cross join these, like so:

SELECT V.Village, F.Firstname 
FROM (SELECT DISTINCT Village FROM MANJOLOHIGH) AS V 
CROSS JOIN (SELECT DISTINCT Firstname FROM MANJOLOHIGH) AS F

Given the fact that you are talking about five columns, I'd strongly suggest the latter. Given a table of 1000 records, the first resultset would result in 1000 * 1000 * 1000 * 1000 * 1000 records, before DISTINCT is applied.

Upvotes: 0

MEdwin
MEdwin

Reputation: 2960

@Lanceleazol, see the modified below: You will need a distict to prevent double entries appearing.

SELECT DISTINCT V.Village, F.Firstname 
FROM MANJOLOHIGH AS V 
CROSS JOIN ( SELECT DISTINCT F.Firstname 
                FROM
                 MANJOLOHIGH AS F) F

Upvotes: 1

Lanceleazol
Lanceleazol

Reputation: 53

By naming the columns and aliasing the same table differently meet the requirements of producing a Cartesian product, like this:

SELECT V.Village, F.Firstname 
FROM MANJOLOHIGH AS V 
CROSS JOIN MANJOLOHIGH AS F;

This code will produce a resultset of V.Village rows multiplied by F.Firstname rows.

Upvotes: 1

Related Questions