Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Generate Possible combinations of a number puzzle

This question was asked to me by a close friend, Parveen. He has been a good friend and very very good MS-BI & SQL Architect. Here we have to generate combinations of a number. E.g. For a number 123 we can have 6 different combinations like 132,123,231,213,321,312.

Please check out the sample input values and sample expected output below.

Sample Input

DECLARE @ AS INT = 123

Expected Output

Combinations
132
123
231
213
321
312

Script

Use below script to create table and insert sample data into it.

--

DECLARE @ AS INT = 123

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1 | Using Dynamic SQL & Numbers Table


--

IF OBJECT_ID('Tempdb..#T') IS NOT NULL
	DROP TABLE tempdb..#T

DECLARE @ AS INT = 123

IF LEN(@) <= 7 

BEGIN 

DECLARE @str AS VARCHAR(100) 
SET @str = CAST(@ AS VARCHAR(100)) 

DECLARE @cols AS VARCHAR(100) = '' 

       SELECT DISTINCT SUBSTRING(@str,NUMBER,1) n INTO #T FROM MASTER..spt_values WHERE number > 0 AND number <= LEN(@) 

       SELECT @cols = @cols + r 
       FROM ( SELECT DISTINCT CONCAT(', o',number,'.n') r FROM MASTER..spt_values WHERE number > 0 AND number <= (LEN(@)-1) 	)q

	DECLARE @ExecStr AS VARCHAR(1000) = ''
	SET @ExecStr = 'SELECT CAST(CONCAT( a.n' + @cols  +  ' ) AS INT) Combinations FROM #T a'
	
	SELECT @ExecStr = @ExecStr + r FROM 
	(
					SELECT DISTINCT CONCAT(' CROSS APPLY ( SELECT * FROM #T b' , number , ' WHERE ( b' , number, '.n' , ' <> a.n ) ', 
					CASE WHEN number = 1 then '' 
						 WHEN number = 2 then ' AND ( b2.n <> o1.n )'   
						 WHEN number = 3 then ' AND ( b3.n <> o1.n )  AND ( b3.n <> o2.n ) '	 
						 WHEN number = 4 then ' AND ( b4.n <> o1.n )  AND ( b4.n <> o2.n ) AND ( b4.n <> o3.n ) '	 
						 WHEN number = 5 then ' AND ( b5.n <> o1.n )  AND ( b5.n <> o2.n ) AND ( b5.n <> o3.n ) AND ( b5.n <> o4.n ) '	 
						 WHEN number = 6 then ' AND ( b6.n <> o1.n )  AND ( b6.n <> o2.n ) AND ( b6.n <> o3.n ) AND ( b6.n <> o4.n ) AND ( b6.n <> o5.n ) '	 
					END
					 ,') o' , number  ) r FROM 
					MASTER..spt_values 
					WHERE number > 0 AND number <= (LEN(@)-1)
	)p

	EXEC (@ExecStr)
END

IF OBJECT_ID('tempdb..#T') IS NOT NULL
	DROP TABLE tempdb..#T

	
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal Http://MSBISkills.com

Advertisements