How do I translate this query from MS Access into SQL Server
Not quite sure I follow as I rarely use Access, but I believe this is what you're looking for:

SELECT -SUM(
   CASE WHEN 12=[GroupNum] THEN 1 ELSE 0 END
   * [BoardStatus]
   ) AS G12
[...]


CASE...WHEN is the inline "if" of T-SQL.