10

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
  • So just to be clear, you want to take the name column, split it by the space character into rows, and then get every possible combination of names between those rows? – George.Palacios May 8 '19 at 7:51
  • @George.Palacios, Yes exactly. – MAK May 8 '19 at 7:53
  • Should they all be 3 names long? Or do you want every possible combination? – George.Palacios May 8 '19 at 7:59
  • @George.Palacios, They may comes with any numbers of words. – MAK May 8 '19 at 8:02
15

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 dbfiddle logodb<>fiddle.uk.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.