SELECT

  d.[provider],
  d.[ci_name] Usluga,
  dc.[ci_name] Sostav,
  dc.[support_groups],
  cnt.CONTACT_NAME

FROM
  dbo.DEVICE2M1 D
  LEFT JOIN [dbo].[CIRELATIONSM1] C ON (
        c.[logical_name] = d.[logical_name] )
  LEFT JOIN dbo.DEVICE2M1 dc ON (
        dc.[logical_name] = c.[relationship_name] )
  LEFT JOIN dbo.ASSIGNMENTA1 a ON (
        d.[support_groups] = a.[name] )
  LEFT JOIN dbo.CONTCTSM1 cnt ON (
        a.[operators] = cnt.[OPERATOR_ID] )
WHERE
  d.[type] = 'bizservice' and c.RELATIONSHIP_NAME like '%oprtn%'
  and ISNULL( D.CI_NAME,'пусто') in (@Service)
  and ISNULL( d.[provider],'пусто') in (@Provider)
  ORDER BY  d.[provider],Usluga,Sostav