Ttdfjt

I have the following sample data for permutations and combination.

create table tbltest
(
 name varchar(50),
 addres varchar(100)
);

insert into tbltest values('Sam Mak John','Street 1 HNo 101 USA');
insert into tbltest values('Donatella Nobatti','HNo 101 UK');
insert into tbltest values('Sam Buca','Main Road B Block UAE');
insert into tbltest values('Juan Soponatime','Hight Street CA');
insert into tbltest values('Aaron Spacemuseum','HNo A10 100 feet Road A Block ');

I want to generate permutations and combinations for the name column in the table tbltest and store into temp table.

For an example expected result:

name
----------------
John Mak Sam
John Sam Mak
Mak John Sam
Mak Sam John
Sam John Mak
Sam Mak John
....
....

Tried with following query: Source

Note: Following query works as expected for single record but when I tried on table using cursor its running more than 35 min and still keeps running.

DECLARE @inputStr VARCHAR(MAX)= 'Sam Mak John';
DECLARE @ValueStr VARCHAR(100);
DECLARE @Count INT, @Loop INT= 1, @totalSum INT;
DECLARE @Query1 VARCHAR(1000), @Query2 VARCHAR(1000), @Query3 VARCHAR(1000), @Query4 VARCHAR(1000), @Query5 VARCHAR(1000), @Query6 VARCHAR(1000), @Query VARCHAR(4000), @Combination VARCHAR(1000);

--Temporary table to capture all the words separately
CREATE TABLE #tmpvalues
(intIndex INT IDENTITY(1, 1), 
 intProc INT, 
 subStr VARCHAR(100)
);

--Temporary table to store all the possible combinations
CREATE TABLE #tmpCombinations
(subCombStr VARCHAR(1000)
); 

--get the sub-strings(words) from input statement into a temp table
WHILE LEN(@inputStr) > 0
 BEGIN
 SET @ValueStr = LEFT(@inputStr, ISNULL(NULLIF(CHARINDEX(' ', @inputStr) - 1, -1), LEN(@inputStr)));
 SET @inputStr = SUBSTRING(@inputStr, ISNULL(NULLIF(CHARINDEX(' ', @inputStr), 0), LEN(@inputStr)) + 1, LEN(@inputStr));
 INSERT INTO #tmpvalues
 VALUES
 (@Loop, 
 @ValueStr
 );
 SET @Loop = @Loop + 1;
 END;
SELECT @Count = MAX(intINDEX)
FROM #tmpvalues;
SET @Loop = 1;

--Set an integer values for each words
--This will be used to filter the combinations in which any two words are repating
DECLARE @tempIntAdd INT;--Addition factor
SET @tempIntAdd = @Loop * @Count;
WHILE @Loop <= (@Count - 1)
 BEGIN
 DECLARE @tempIntProc INT;
 SELECT @tempIntProc = intProc
 FROM #tmpvalues
 WHERE intIndex = @Loop;
 UPDATE #tmpvalues
 SET 
 intProc = @tempIntProc + @tempIntAdd
 WHERE intIndex = @Loop + 1;
 SET @Loop = @Loop + 1;
 SET @tempIntAdd = @tempIntAdd * 2;
 END;
--
SET @Loop = 1;
SET @Query1 = 'INSERT INTO #tmpCombinations SELECT DISTINCT ';
SET @Query2 = 'ALL_COMBINATIONS FROM';
SET @Query3 = ' ';
SET @Query4 = ' WHERE';
SET @Query5 = '(';
SET @Query6 = ')';

-- Generate the dynamic query to get permutations and combination of individual words
WHILE @Loop <= @Count
 BEGIN
 SELECT @ValueStr = subStr
 FROM #tmpvalues
 WHERE intIndex = @Loop;
 SET @Query1 = @Query1 + 'T' + CAST(@Loop AS VARCHAR) + '.subStr ';
 IF(@Loop < @Count)
 SET @Query1 = @Query1 + '+ '' '' + ';
 SET @Query3 = @Query3 + '#tmpvalues ' + 'T' + CAST(@Loop AS VARCHAR);
 IF(@Loop < @Count)
 SET @Query3 = @Query3 + ', ';
 SET @Query5 = @Query5 + 'T' + CAST(@Loop AS VARCHAR) + '.intProc';
 IF(@Loop < @Count)
 SET @Query5 = @Query5 + ' + ';
 SET @Loop = @Loop + 1;
 END;
SELECT @totalSum = SUM(intProc)
FROM #tmpvalues;

--Create final query
SET @Query = @Query1 + @Query2 + @Query3 + @Query4 + @Query5 + @Query6 + ' =' + CAST(@totalSum AS VARCHAR);

--Execute the dynamic Query
EXECUTE (@Query);

SELECT subCombStr from #tmpCombinations

If you split each name into a separate table of the following structure,

CREATE TABLE dbo.NameParts
(
 ID int NOT NULL,
 NamePart varchar(30) NOT NULL
);

(where ID marks name parts that belong to the same name, to avoid permutation of parts of different names) – then you can use a recursive CTE to produce the permutations:

WITH
 permutations AS
 (
 SELECT
 ID = t.ID,
 FullNameVariation = CAST(t.NamePart AS varchar(500)),
 Level = COUNT(*) OVER (PARTITION BY t.ID)
 FROM
 dbo.NameParts AS t
 UNION ALL
 SELECT
 ID = t.ID,
 FullNameVariation = CAST(p.FullNameVariation + ' ' + t.NamePart AS varchar(500)),
 Level = p.Level - 1
 FROM
 dbo.NameParts AS t
 INNER JOIN permutations AS p ON t.ID = p.ID
 WHERE 1=1
 AND p.Level > 1
 AND ' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'
 )
SELECT
 ID,
 FullNameVariation
FROM
 permutations
WHERE
 Level = 1
ORDER BY
 ID,
 FullNameVariation
;

Applying the query to this data sample:

INSERT INTO dbo.NameParts
VALUES
 (1, 'Sam'), (1, 'Mak'), (1, 'John'),
 (2, 'Donatella'), (2, 'Nobatti'),
 (3, 'Sam'), (3, 'Buca'),
 (4, 'Juan'), (4, 'Soponatime'),
 (5, 'Aaron'), (5, 'Spacemuseum')
;

produces the following output:

ID FullNameVariation
-- -----------------
1 John Mak Sam
1 John Sam Mak
1 Mak John Sam
1 Mak Sam John
1 Sam John Mak
1 Sam Mak John
2 Donatella Nobatti
2 Nobatti Donatella
3 Buca Sam
3 Sam Buca
4 Juan Soponatime
4 Soponatime Juan
5 Aaron Spacemuseum
5 Spacemuseum Aaron

You can play with this solution using a live demo at db<>fiddle.uk.