kwuh97 kwuh97 - 3 months ago 18
SQL Question

SQL Server possible combinations

The returned result set should concatenate the text column based on the type columns:

I have a SQL Server table:

id, type, text
1, 1, C#
2, 1, VB
3, 1, Java
4, 1, JQuery
5, 1, SQL
6, 2, Basic
7, 2, Intermediate
8, 2, Advance
9, 2, Not Applicable


I would like to return a result set that looks like this:

Result
C#/Basic
C#/Intermediate
C#/Advance
C#/Not Applicable
VB/Basic
VB/Intermediate
VB/Advance
VB/Not Applicable
Java/Basic
Java/Intermediate
Java/Advance
Java/Not Applicable
JQuery/Basic
JQuery/Intermediate
JQuery/Advance
JQuery/Not Applicable
SQL/Basic
SQL/Intermediate
SQL/Advance
SQL/Not Applicable


Thanks in Advance

Answer
Declare @Table table (id int, type int, text varchar(50))
Insert Into @Table values
(1, 1, 'C#'),
(2, 1, 'VB'),
(3, 1, 'Java'),
(4, 1, 'JQuery'),
(5, 1, 'SQL'),
(6, 2, 'Basic'),
(7, 2, 'Intermediate'),
(8, 2, 'Advance'),
(9, 2, 'Not Applicable')


Select A.Text+'/'+B.Text
 From  @Table A
 Join  @Table B on (A.Type=1 and B.Type=2)
 Order By 1

Returns

C#/Advance
C#/Basic
C#/Intermediate
C#/Not Applicable
Java/Advance
Java/Basic
Java/Intermediate
Java/Not Applicable
JQuery/Advance
JQuery/Basic
JQuery/Intermediate
JQuery/Not Applicable
SQL/Advance
SQL/Basic
SQL/Intermediate
SQL/Not Applicable
VB/Advance
VB/Basic
VB/Intermediate
VB/Not Applicable