Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Homework
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 18:51pm
Edited 22-Feb-12 19:02pm
Prerak Patel140.6K
v2
Comments
Varun Sareen at 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
0 OriginalGriff 7,105
1 DamithSL 5,079
2 Maciej Los 4,866
3 Sergey Alexandrovich Kryukov 4,617
4 Kornfeld Eliyahu Peter 4,384


Advertise | Privacy | Mobile
Web04 | 2.8.141223.1 | Last Updated 23 Feb 2012
Copyright © CodeProject, 1999-2014
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