0

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.

1

I'm not 100% sure what you're trying to achieve, but would something like this solve if for you? (If so, I'm sure there's a more effective method as well).

SELECT 
BV.Title, 
BV2.Title 
FROM dbo.BaseValues AS BV
CROSS APPLY dbo.BaseValues AS BV2 
WHERE bv.Title <> BV2.Title
AND BV.ID <= BV2.ID  


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
    AND BV1.ID <= BV2.ID
    AND BV2.ID <= BV3.ID


    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
    AND BV1.ID <= BV2.ID
    AND BV2.ID <= BV3.ID
    AND BV3.ID <= BV4.ID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.