I am trying to condense what I already have in a scalar function. In a table, PERSONS, I have multiple people with multiple roles in a department. Table PERSONS looks like this:

```
id      name            dept         role     
-------------------------------------------
1       John       studentaffairs    Coder    
2       Joe        studentaffairs    Intern
3       Charlie    studentaffairs     Dist
4       Dan        studentaffairs     Dist
5       Rita       studentaffairs     Lead
6       Nuna       studentaffairs     Lead

```

My function identifies who the person is (the INPUT) and returns a compact view of generally what role they have in the department. 

```
alter function get_simplifiedrolesfordistribution(@name nvarchar(30))
returns varchar(max)
As
Begin
    DECLARE @AudienceTag varchar(255);

    select @AudienceTag = ('studentaffairs, 
                            studentaffairs-'+ IIF(c.role = 'Coder', 'general', '')+
                                              IIF(c.role = ' Volunteer','general','')+
                                              IIF(c.role = ' Intern','summer','')+
                                              IIF(c.role = ' Dist','seasonal','')+
                                              IIF(c.role = ' Lead','manager','')
                          )
    from PERSONS c
    where c.name = @name ;

    Return @AudienceTag;
End;

```

Testing case: 

INPUT - Nuna

Result - studentaffairs, studentaffairs-manager

My question: Is there a simplified way of developing the scalar function in sql? I have multiple tables like the one above that are way bigger. It is possible to type up all the different roles and fitting them to a general category, but it't not ideal. 

Thanks for any help.