with cte1 (id,parentid)
  as (select id
            ,parentid
        from @t
      union all
      select a.id
            ,b.parentid
        from cte1 as a
             inner join @t as b
                     on b.id=a.parentid
       where b.parentid is not null
     )
    ,cte2 (id,parentids)
  as (select id
            ,stuff((select ','+cast(parentid as varchar(max))
                      from cte1 as b
                     where b.id=a.id
                     order by parentid
                       for xml path ('')
                   )
                   ,1,1,''
                  ) as parentids
        from @t as a
       where parentid is not null
     )
    ,cte3 (id,nextids)
  as (select distinct
             parentid as id
            ,stuff((select ','+cast(id as varchar(max))
                      from cte1 as b
                     where b.parentid=a.parentid
                     order by id
                       for xml path ('')
                   )
                   ,1,1,''
                  ) as nextids
        from @t as a
       where parentid is not null
     )
select a.id
      ,a.name
      ,b.parentids
      ,c.nextids
  from @t as a
       left outer join cte2 as b
                    on b.id=a.id
       left outer join cte3 as c
                    on c.id=a.id
 order by a.id

id name      parentids nextids
1  category1 NULL      2,3,4
2  category2 1         3,4
3  category3 1,2       NULL
4  category4 1,2       NULL
5  category5 NULL      6
6  category6 5         NULL