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)
|
