Case Statement to produce the following results

http://msdn.microsoft.com/en-us/library/ms181765.aspx[^]
Try and learn.

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

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