Click here to Skip to main content
12,350,134 members (26,947 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
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
ADAMS      Other
JAMES      Other
Posted 22-Feb-12 17:51pm
Edited 22-Feb-12 18:02pm
Prerak Patel141.7K
v2
Comments
Varun Sareen 23-Feb-12 0:02am
   
Please give some clarity :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

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. Smile | :)
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

I did't understand yt requirement , can up plz provide other details
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
ADAMS Other
JAMES Other
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 23 Feb 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100