12,895,064 members (49,693 online)
Rate this:
See more:
Use a CASE statement to display alternative job titles as follows

```Original Title		Displayed title
--------------		---------------
PRESIDENT		CEO
MANAGER			Director
SALESMAN		Consultant
ANALYST			Other
CLERK			Other

ENAME      JOB_TITLE
---------- ----------
KING       CEO
ALLEN      Consultant
WARD       Consultant
TURNER     Consultant
MARTIN     Consultant
SCOTT      Other
FORD       Other
JONES      Director
CLARK      Director
BLAKE      Director
SMITH      Other
MILLER     Other
JAMES      Other```
Posted 22-Feb-12 17:51pm
Updated 22-Feb-12 18:02pm
v2
Varun Sareen 23-Feb-12 0:02am

Please give some clarity :)

Rate this:

## Solution 3

Hi, Use below query

```Select EName, Case When JOB_TITLE = 'PRESIDENT' then 'CEO' when JOB_TITLE = 'MANAGER' then 'Director' when JOB_TITLE = 'SALESMAN' then 'Consultant' else 'OTHER' end
from TABLENAME```

Use this,Hopefully it will fulfill your goal. :-)
Rate this:

Rate this:

## Solution 2

I did't understand yt requirement , can up plz provide other details
Rate this:

## Solution 4

Hi,
I check it my self with Switch Case its not possible i think use the following Technique. You will use your table name on of #temp
Create "Scaler-valued Function"
```CREATE function SplitFun(@sInputList VARCHAR(8000), @sDelimiter VARCHAR(8000) = ',')
returns varchar(200)
as
begin
declare @sItem varchar(200)
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
RETURN @sInputList
end```

It will return you Single Value of Designation then you can achieve you goal like this

```select * from #temp
where dbo.SplitFun([ENAME JOB_TITLE], ' ') = 'CEO'
UNION ALL
select * from #temp
where dbo.SplitFun([ENAME JOB_TITLE], ' ') = 'Director'
UNION ALL
select * from #temp
where dbo.SplitFun([ENAME JOB_TITLE], ' ') = 'Consultant'
UNION ALL
select * from #temp
where dbo.SplitFun([ENAME JOB_TITLE], ' ') = 'Other'```

this will return you the following Result
```KING CEO
JONES Director
CLARK Director
BLAKE Director
ALLEN Consultant
WARD Consultant
TURNER Consultant
MARTIN Consultant
SCOTT Other
FORD Other
SMITH Other
MILLER Other
JAMES Other```

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Jochen Arndt 155 Richard MacCutchan 118 ppolymorphe 110 OriginalGriff 110 CHill60 100
 OriginalGriff 5,172 CHill60 3,528 Jochen Arndt 2,958 Karthik Bangalore 2,456 ppolymorphe 2,285

Advertise | Privacy | Mobile
Web02 | 2.8.170424.1 | Last Updated 23 Feb 2012