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

Id ValueId vname
1 1 A
2 2 B1
6 2 B2
3 3 C1
4 3 C2
5 3 C3

Expected Output

(No column name)
A-B1-C1
A-B1-C2
A-B1-C3
A-B2-C1
A-B2-C2
A-B2-C3

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”.

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