This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing an advanced SQL Query to find a Permutations and Combinations of given string data of the table. The question is look like very complex query, but the solution is very simple.
You can apply self-joins and can prepare all available combinations. The T-SQL or Database Developer can practice this query before an interview.
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE tbl_StringAllCombinations (ID INT,TextData CHAR(1)) GO INSERT INTO tbl_StringAllCombinations VALUES (1,'A') ,(2,'B') ,(3,'C') ,(4,'D') GO |
The Solution: (This is my work around solution, Interviewer may ask for any other alternative solution so please also try to prepare it and share your comments…)
|
1 2 3 4 5 6 |
SELECT a.TextData, b.TextData, c.TextData, d.TextData FROM tbl_StringAllCombinations a join tbl_StringAllCombinations b on b.TextData not in (a.TextData) join tbl_StringAllCombinations c on c.TextData not in (a.TextData, b.TextData) join tbl_StringAllCombinations d on d.TextData not in (a.TextData, b.TextData, c.TextData) order by 1, 2, 3, 4 |
The Result:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
TextData TextData TextData TextData -------- -------- -------- -------- A B C D A B D C A C B D A C D B A D B C A D C B B A C D B A D C B C A D B C D A B D A C B D C A C A B D C A D B C B A D C B D A C D A B C D B A D A B C D A C B D B A C D B C A D C A B D C B A (24 row(s) affected) |
