The most elegant way to generate permutations in SQL server

user_201142 0
Viewed 32176

Given a the following table:

Index | Element
---------------
  1   |    A
  2   |    B
  3   |    C
  4   |    D

We want to generate all the possible permutations (without repetitions) using the elements. the final result (skipping some rows) will look like this:

  Results
----------
   ABCD
   ABDC
   ACBD
   ACDB
   ADAC
   ADCA

   ...

   DABC
   DACB
   DBCA
   DBAC
   DCAB
   DCBA

  (24 Rows)

How would you do it?

permutation tsql sql-server sql
10 Answers

--Hopefully this is a quick solution, just change the values going into #X

IF OBJECT_ID('tempdb.dbo.#X', 'U') IS NOT NULL  DROP TABLE #X; CREATE table #X([Opt] [nvarchar](10) NOT NULL)
Insert into #X values('a'),('b'),('c'),('d')
declare @pSQL NVarChar(max)='select * from #X X1 ', @pN int =(select count(*) from #X), @pC int = 0;
while @pC<@pN begin
if @pC>0 set  @pSQL = concat(@pSQL,' cross join #X X', @pC+1);
set @pC = @pC +1;
end
execute(@pSQL)

--or as single column result

IF OBJECT_ID('tempdb.dbo.#X', 'U') IS NOT NULL  DROP TABLE #X; CREATE table #X([Opt] [nvarchar](10) NOT NULL)
Insert into #X values('a'),('b'),('c'),('d')
declare @pSQL NVarChar(max)=' as R from #X X1 ',@pSelect NVarChar(Max)=' ',@pJoin NVarChar(Max)='', @pN int =(select count(*) from #X), @pC int = 0;
while @pC<@pN begin
if @pC>0 set  @pJoin = concat(@pJoin ,' cross join #X X', @pC+1) set @pSelect =  concat(@pSelect ,'+ X', @pC+1,'.Opt ')
set @pC = @pC +1;
end
set @pSQL = concat ('select X1.Opt', @pSelect,@pSQL ,@pJoin)
exec(@pSQL)
user_38724390
Related