How to use case

1

I have to report on our programs and the worker roles that are in use for that program.
select pi.program_name, wr.worker_role_id,
case when worker_role_id = 'D229E343-SC3F-4129-8084-09DQCVA2229B' then ' Service Provider Supervisor'

case when worker_role_id = 'DE72B3A2-F413-429A-B109-0C5CF0B6OLP5' then ' Service Provider'

from worker_role_program_link_view wr
inner join program_info pi
on wr.program_info_id = pi.program_info_id

I cannot do this fully in SQL because there are programs that share the same worker role id but I don't mind doing it using case if i have to as above.
However this case does not compile for more than one case. I am not sure how to code multiple case statements.
There are less than 100 it will be ok this way if I can be informed how to do this.

2

If you want one column and don't want to change the code much then it would be someting like this:

select pi.program_name, wr.worker_role_id,
case
when worker_role_id = 'D229E343-SC3F-4129-8084-09DQCVA2229B' then ' Service Provider Supervisor'
when worker_role_id = 'DE72B3A2-F413-429A-B109-0C5CF0B6OLP5' then ' Service Provider'
else
''
end AS worker_role_name
from worker_role_program_link_view wr
inner join program_info pi
on wr.program_info_id = pi.program_info_id;

if you want multiple columns then it would be something like this:

select pi.program_name, wr.worker_role_id,
case when worker_role_id = 'D229E343-SC3F-4129-8084-09DQCVA2229B' then ' Service Provider Supervisor' else '' end as [Service Provider Supervisor],
case when worker_role_id = 'DE72B3A2-F413-429A-B109-0C5CF0B6OLP5' then ' Service Provider'
else '' end as [Service Provider]
from worker_role_program_link_view wr
inner join program_info pi
on wr.program_info_id = pi.program_info_id;

A better approch will be te create a new table with id and role_name or maybe a view, so you have to change it once instead of every query where you going to use this statement.