Soma recursiva na estrutura em árvore
Eu tenho uma estrutura de árvore em uma única tabela. A tabela é uma árvore de categorias que podem ser aninhadas infinitamente. Cada categoria possui uma coluna ProductCount que informa quantos produtos estão diretamente na categoria (sem somar categorias filho).
Id | ParentId | Name | ProductCount
------------------------------------
1 | -1 | Cars | 0
2 | -1 | Bikes | 1
3 | 1 | Ford | 10
4 | 3 | Mustang | 7
5 | 3 | Focus | 4
Gostaria de fazer uma consulta sql que, para cada linha / categoria, me forneça o número de produtos, incluindo os das categorias filho.
A saída para a tabela acima deve ser
Id | ParentId | Name | ProductCount | ProductCountIncludingChildren
--------------------------------------------------------------------------
1 | -1 | Cars | 0 | 21
2 | -1 | Bikes | 1 | 1
3 | 1 | Ford | 10 | 21
4 | 3 | Mustang | 7 | 7
5 | 3 | Focus | 4 | 4
Eu sei que provavelmente deveria usar CTE, mas não consigo fazê-lo funcionar da maneira que deveria.
Qualquer ajuda é apreciada!