You can use ROW_NUMBER to solve the problem

select 
        
       case 
           when COS_AD_memberOfn=1 then 'VLAN115'
           when COS_AD_memberOfn=2 then 'VLAN116'
           when COS_AD_memberOfn=3 then 'VLAN118'
           else 'other' end as Name
       ,c
from (
        select *,count(*) over(partition by COS_AD_memberOfn ) as c
               ,ROW_NUMBER() over(partition by COS_AD_memberOfn order by COS_SID ) as rw
        from (
                select
                case
                       when COS_AD_memberOf LIKE '%VLAN115%' then 1
                       when COS_AD_memberOf LIKE '%VLAN116%' then 2
                       when COS_AD_memberOf LIKE '%VLAN118%' then 3
                       else  0 end as COS_AD_memberOfn
                       ,COS_SID
                       ,COS_AD_name
                       ,COS_AD_memberOf
                from CacheOrganizationStructure
                WHERE COS_AD_name LIKE '%VLAN115%' OR COS_AD_name
                    LIKE '%VLAN116%' OR COS_AD_name
                    LIKE '%VLAN118%'
        )a
        

)a
where rw=1
ORDER BY a.COS_AD_name ASC

example:

expected

You can use PATINDEX inside a subquery to calulcate the relevant value, then use a windowed count using PARTITION BY to get that.

SELECT
  *,
  count = COUNT(*) OVER (PARTITION BY v.VlanValue)
FROM CacheOrganizationStructure
CROSS APPLY (VALUES(
    CASE WHEN PATINDEX('%VLAN115%', COS_AD_memberOf) > 0 THEN 115
         WHEN PATINDEX('%VLAN116%', COS_AD_memberOf) > 0 THEN 116
         WHEN PATINDEX('%VLAN118%', COS_AD_memberOf) > 0 THEN 118
    END
)) v(VlanValue);

An alternative is to use a combination of PATINDEX and SUBSTRING to pull the value out.

SELECT
  *,
  count = COUNT(*) OVER (PARTITION BY v.VlanValue)
FROM CacheOrganizationStructure
CROSS APPLY (VALUES(
    SUBSTRING(COS_AD_memberOf, NULLIF(PATINDEX('%VLAN[0-9][0-9][0-9]%', COS_AD_memberOf), 0), 7)
)) v(VlanValue);

You don't have to use windowing functions. A normal GROUP BY also works, and will aggregate up all the rows.

SELECT
  v.VlanValue,
  count = COUNT(*),
  Sids = STRING_AGG(COS_SID, ', ')
FROM CacheOrganizationStructure
CROSS APPLY (VALUES(
    SUBSTRING(COS_AD_memberOf, NULLIF(PATINDEX('%VLAN[0-9][0-9][0-9]%', COS_AD_memberOf), 0), 7)
)) v(VlanValue)
GROUP BY
  v.VlanValue;

SQL Fiddle