title: Check String with Multiple Column Results
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My function identifies who the person is (the INPUT) and returns a compact view of generally what role they have in the department. </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Testing case: </p>
  
  <p>INPUT - Nuna</p>
  
  <p>Result - studentaffairs, studentaffairs-manager</p>
  
  <p>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. </p>
  
  <p>Thanks for any help.</p>