this should actually be not a too hard task, but it seems to be a little too early for me to see the solution:
I have 4 different string values.
I want to have all combinations of those 4 values where not all values have to be present, so combining all values by cross joining those values zero times, one time, two times and three times. Like I also demonstrate in this fiddle:
CREATE TABLE BaseValues (ID INT PRIMARY KEY IDENTITY(1,1), Title VARCHAR(63)); INSERT BaseValues (Title) SELECT 'One' UNION SELECT 'Two' UNION SELECT 'Three' UNION SELECT 'Four'; SELECT Title AS T1 FROM BaseValues; SELECT BV1.Title AS T1, BV2.Title AS T2 FROM BaseValues BV1 CROSS JOIN BaseValues BV2 WHERE BV1.Title <> BV2.Title; SELECT BV1.Title AS T1, BV2.Title AS T2, BV3.Title AS T3 FROM BaseValues BV1 CROSS JOIN BaseValues BV2 CROSS JOIN BaseValues BV3 WHERE BV1.Title <> BV2.Title AND BV1.Title <> BV3.Title AND BV2.Title <> BV3.Title; SELECT BV1.Title AS T1, BV2.Title AS T2, BV3.Title AS T3, BV4.Title AS T4 FROM BaseValues BV1 CROSS JOIN BaseValues BV2 CROSS JOIN BaseValues BV3 CROSS JOIN BaseValues BV4 WHERE BV1.Title <> BV2.Title AND BV1.Title <> BV3.Title AND BV1.Title <> BV4.Title AND BV2.Title <> BV3.Title AND BV2.Title <> BV4.Title AND BV3.Title <> BV4.Title;
Now this is almost the result I want, except I still have duplicates inside of that.
I do not care in what order values appear in my result set I just want to have rows in which those combination of values are unique; for example from the second result set here the rows containing
One Two and
Two One would be equivalent to me and I just want one of them.
I thought maybe I could concatenate all columns from my result set into one column that contains those values in alphabetical order so a
SELECT DISTINCT would eliminate my undesired duplicates.
But I have not been able to figure out how I could do that or maybe there is another more convenient way?
Any hints are appreciated.