Try this
Declare @Course table ([Course Name] Varchar(100),[Course Type] Int)
Insert Into @Course
Select 'MCA',1 Union All
Select 'MCA',2
;With CTE AS(
Select [Course Type] = 1,[Course Description] = 'Regular' Union All
Select [Course Type] = 2,[Course Description] = 'Distance'
)
Select
Result =
c.[Course Name]
+ '('
+ CTE.[Course Description]
+ ')'
From @Course c
Join CTE ON c.[Course Type] = CTE.[Course Type]
If you are using Denali CTP 3, you can use the new Choose function
Select
Result =
Concat(
c.[Course Name]
,'('
,CTE.[Course Description]
,')'
)
From @Course c
Join CTE ON c.[Course Type] = CTE.[Course Type]
Output
Result
MCA(Regular)
MCA(Distance)
Niladri Biswas