SQL Query Select

reogeo2008

Hi all, Can anybody help me in the following issue. I have a course table contains coursename and type I want to select course name and type and the display should like Coursename(type) th problem is my type values are integers.. For example 1 for Regular type ,2 for distance type so if type is 1 and coursename is MCA Output should be MCA(Regular) is it possiblle thru the sql statement Thanks in advance, Reena

Blue_Boy

I guess you have to have linked table for type of course, Here is example how it should be

select
case when [type]=1 then 'MCA' when [type]=2 then 'Another Title' end + ' (' +
coursename +')' as TypeAndCourse
from courses

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

Mycroft Holmes

Create a table of CourseTypes with a PK field and a description. This is then an extensible solution if you need to add another type. Create a view with an inner join (assumes the type is required in the course table and that a course can only have 1 type) to the type table and include the course type description field (I name the view vwCourse) Or just do it the way the Blue one suggested!

Never underestimate the power of human stupidity RAH

AditSheth

Blue_Boy wrote:

select case when [type]=1 then 'MCA' when [type]=2 then 'Another Title' end + ' (' + coursename +')' as TypeAndCourse from courses

just little changes

SELECT [Coursename] + '(' + case when [type] = 1 THEN 'Regular' when [type]=2 then 'Another Title' end + ')' as TypeAndCourse
from courses

but i am agree with "Mycroft Holmes" suggestion of creating new table..

Where can we go to find God if we cannot see Him in our own hearts and in every living being -Swami Vivekananda

Niladri_Biswas

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