SQL Puzzle | The Permutation Puzzle

In this puzzle you have to generate the permutation in a sequence.For more details please check the sample input and expected output.

Sample Input

Expected Output

Script

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

--

 CREATE TABLE Permutation
(
   Id INT
  ,ValueId INT
  ,vname VARCHAR(10)
)
GO

INSERT INTO Permutation VALUES
(1,1,'A'),
(2,2,'B1'),
(6,2,'B2'),
(3,3,'C1'),
(4,3,'C2'),
(5,3,'C3')
GO

--

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

--

SELECT 
	DISTINCT CONCAT(sc.vname,'-',sc1.vname,'-',sc2.vname) 
FROM Permutation sc 
INNER JOIN Permutation sc1 on sc1.ValueId > sc.ValueId
INNER JOIN Permutation sc2 on sc2.ValueId > sc1.ValueId
 
--

Output-1

--

/*------------------------
SELECT 
	DISTINCT CONCAT(sc.vname,'-',sc1.vname,'-',sc2.vname) 
FROM Permutation sc 
INNER JOIN Permutation sc1 on sc1.ValueId > sc.ValueId
INNER JOIN Permutation sc2 on sc2.ValueId > sc1.ValueId
------------------------*/

--------------------------------
A-B1-C1
A-B1-C2
A-B1-C3
A-B2-C1
A-B2-C2
A-B2-C3

(6 row(s) affected)



--

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