12,350,134 members (26,947 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
Edited 22-Feb-12 18:02pm
Prerak Patel141.7K
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
 OriginalGriff 163 ppolymorphe 150 Richard MacCutchan 50 Sergey Alexandrovich Kryukov 43 Member 12599256 32
 OriginalGriff 8,550 Sergey Alexandrovich Kryukov 5,980 Dave Kreskowiak 2,834 ppolymorphe 2,256 Richard MacCutchan 2,169

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