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