I have a collection named Sales with columns Product, Region, and Amount. I want to pivot this data based on the Region column. Can someone provide a concise example of how to achieve this transformation and explain the key components of the syntax involved? Additionally, how would this query be modified if the regions are dynamic or unknown, requiring the use of dynamic SQL?
Your insights will greatly assist me in this data restructuring task.