0

Given 3 values, such as

+--------+
| values |
+--------+
|    1   |
+--------+
|    2   |
+--------+
|    3   |
+--------+

Is it possible to insert every UNIQUE combination of each value into a table without using a WHILE loop?

For example, the end result would look like:

enter image description here

It's relatively simple to do this with with nested WHILE loops, but I'd like to know if there are any other approaches that could be used.

devklick
  • 2,000
  • 3
  • 30
  • 47
  • Shouldn't the 4th row of data in the end result table have a 3 in the VALUE_2 column? – knot22 Mar 27 '19 at 01:27
  • @knot22 You're absolutely right! Well spotted, updated now. – devklick Mar 27 '19 at 01:30
  • If you can live with them being concatenated with a delimiter instead of a separate column for value, you might want to look at this: https://stackoverflow.com/questions/3621494/the-most-elegant-way-to-generate-permutations-in-sql-server – Bryce Wagner Mar 27 '19 at 02:17

1 Answers1

0
declare @t table
(
  n int not null
);

insert into @t (n)
values
(1),
(2),
(3);

select * from @t;

select
value1 = t1.n,
value2 = t2.n,
value3 = t3.n
from @t t1
inner join @t t2 on t1.n <> t2.n
inner join @t t3 on t1.n <> t3.n and t2.n <> t3.n;

enter image description here

knot22
  • 2,648
  • 5
  • 31
  • 51
  • Nicely approach! So for every value you have in your data set (say we had 30 values instead of 3), you would need another self-join? – devklick Mar 27 '19 at 01:29
  • For this approach, yes. Are you dealing with 30 values instead of 3? – knot22 Mar 27 '19 at 01:30
  • I'm dealing with any number of values, really. It's quite a hypothetical scenario. Do you know of a more suitable way given a higher number of values? I still think I prefer this (even with 30 values) over a `WHILE` loop. – devklick Mar 27 '19 at 01:32
  • The number of values being dealt with - is that number known before the script executes or is the number of values dynamic with each script execution? – knot22 Mar 27 '19 at 01:35
  • It would be known before the script executes – devklick Mar 27 '19 at 01:36
  • What comes to mind is building dynamic SQL but that might be overkill for this. – knot22 Mar 27 '19 at 01:38