Generate permutations and combination for name column

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 @

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.

StackExchange Database Administrators Q#237619, answer score: 15