SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.NameParts
(
  ID int NOT NULL,
  NamePart varchar(30) NOT NULL
);
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')
;
11 rows affected

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
;


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

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