T-SQL "Dynamic" Join

Question

Given the following SQL Server table with a single char (1) column:

Value ------ '1' '2' '3' 

How to get the following results in T-SQL?

 Result ------ '1+2+3' '1+3+2' '2+1+3' '2+3+1' '3+2+1' '3+1+2' 

This should also be dynamic, so if my table only contains the rows '1' and '2', I would expect:

 Result ------ '1+2' '2+1' 

It seems I should use CROSS JOIN for this, but since I don’t know how many lines will be in front, I’m not sure how many times CROSS JOIN will return to itself ..

 SELECT a.Value + '+' + b.Value FROM MyTable a CROSS JOIN MyTable b WHERE a.Value <> b.Value 

At any given time, there will always be less than 10 (and indeed more like 1-3) rows. Can I do this on the fly in SQL Server?

Edit: ideally, I would like this to happen in one saved proc, but if I need to use another proc or some user-defined functions to do this, I'm fine with that.

Answer 1

This SQL will compute permutations without repetition:

 WITH recurse(Result, Depth) AS ( SELECT CAST(Value AS VarChar(100)), 1 FROM MyTable UNION ALL SELECT CAST(r.Result + '+' + a.Value AS VarChar(100)), r.Depth + 1 FROM MyTable a INNER JOIN recurse r ON CHARINDEX(a.Value, r.Result) = 0 ) SELECT Result FROM recurse WHERE Depth = (SELECT COUNT(*) FROM MyTable) ORDER BY Result 

If MyTable contains 9 lines, it will take some time to calculate, but it will return 362,880 lines.

Update with explanation:

The WITH used to define the expression of a recursive shared table . In fact, the WITH statement is looped several times, executing UNION until the recursion is complete.

The first part of SQL defines the start records. Assuming that there are three lines with the names "A", "B" and "C" in MyTable , this will produce these lines:

  Result Depth ------ ----- A 1 B 1 C 1 

Then the following SQL block executes the first level of recursion:

  SELECT CAST(r.Result + '+' + a.Value AS VarChar(100)), r.Depth + 1 FROM MyTable a INNER JOIN recurse r ON CHARINDEX(a.Value, r.Result) = 0 

These are all the records created so far (which will be in the recurse table), and again joins them with all the records in MyTable . The ON clause filters the list of records in MyTable to return only those that do not exist in this row permutation. This will result in the following lines:

  Result Depth ------ ----- A 1 B 1 C 1 A+B 2 A+C 2 B+A 2 B+C 2 C+A 2 C+B 2 

Then the recursion loops again produce the following lines:

  Result Depth ------ ----- A 1 B 1 C 1 A+B 2 A+C 2 B+A 2 B+C 2 C+A 2 C+B 2 A+B+C 3 A+C+B 3 B+A+C 3 B+C+A 3 C+A+B 3 C+B+A 3 

At this point, recursion stops because UNION does not create more rows, because CHARINDEX will always be 0 .

The last SQL filters all the resulting rows, where the calculated Depth column matches # records in MyTable . This ejects all rows except those created by the last recursion depth. Thus, the end result will be the following lines:

  Result ------ A+B+C A+C+B B+A+C B+C+A C+A+B C+B+A 

Answer 2

You can do this with a recursive CTE:

 with t as ( select 'a' as value union all select 'b' union all select 'c' ), const as (select count(*) as cnt from t), cte as ( select cast(value as varchar(max)) as value, 1 as level from t union all select cte.value + '+' + t.value, 1 + level from cte join t on '+'+cte.value+'+' not like '%+'+t.value+'+%' cross join const where level <= const.cnt ) select cte.value from cte cross join const where level = const.cnt;