One of the problems with using the PIVOT clause in T-SQL is that you have to specify all the spreading column names that you want in your final result set.
To get around this you can generate the column names as strings and then use them within a dynamic SQL query that does the pivotting.
I wish this was an unusual situation… Recently I was asked to develop the ETL process for a system I had never used before. The company that provided the system didn’t want to help or provide documentation to assist in my development. Having been in this situation once or twice before, I knew that I needed to get a handle on a the database structure by looking at foreign keys. Then I needed to create a few diagrams to help me navigate my way around.
BUT… not all the relationships are defined with foreign keys. AND this system is spread across 10 or so databases… So I created this script to help me track down database connectons.