Dynamic Hierarchical Data Pivot with Recursive CTEs

Design a complex pivot table methodology using SQL recursive common table expressions (CTEs) that can dynamically reconstruct organizational hierarchies across multiple levels. Demonstrate how to handle nested reporting structures with variable depth, including methods for calculating rolled-up metrics, identifying reporting paths, and managing potential circular references. Include performance optimization techniques for handling large organizational datasets with more than 10,000 records.