declare @yourtable table (categoriesids varchar(255),registrantid int);

insert into @yourtable
 values ('47',1)
       ,('276|275|278|274|277',4)
       ,('276|275|278|274|277',16261)
       ,(NULL,16262)
       ,(NULL,16264)
       ,(NULL,16265)
       ,(NULL,16266)
       ,(NULL,16267)
       ,(NULL,16268)
       ,(NULL,16269)
       ,(NULL,16270)
       ,('276|275|278',16276)
       ,('276|275|278|274|277',16292)
       ,('276|275|278|274|277',16293)
       ,('276|275|278|274|277',16294)
       ,('276|275|278|274|277',16295)
       ,('276|275|278|274|277',16302)
       ,('276|275|278|274|277',16303)
       ,('276|275|278|274|277',16304)
       ,('276|275|278|274|277',16305)
       ,('276|275|278|274|277',16306)
       ,('276|275|278|274|277',16307)
;
with cte (registrantid,categoriesids,s,e)
  as (select registrantid
            ,isnull(categoriesids,'')+'|' as categoriesids
            ,1 as s
            ,charindex('|',isnull(categoriesids,'')+'|',1) as e
        from @yourtable
      union all
      select registrantid
            ,categoriesids as categoriesids
            ,e+1 as s
            ,charindex('|',categoriesids,e+1) as e
        from cte
       where e<len(categoriesids)
     )
select substring(categoriesids,s,e-s)
      ,count(*)
  from cte
 group by substring(categoriesids,s,e-s)
;