If something like this works then use the following query. I am not sure if it works in SqlServer.
Try to go through Analytical Functions, it will make jobs like this a lot easier. Thanks.



select id,name,
category,count(*) over (partition by category ) categroy_total_count,
children
from test1
order by id;