Eliminate duplicates in different order from all combinations, can I order returned columns alphatically?

Question

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.

Answer 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