I'm in a situation where I need to union multiple Excel sheets with different formatting styles that have been imported into SQL. I've decided on the data format but I can't get every sheet to line up with it and (I think) I need to do a dynamic pivot. I've checked out the other topics relating to my question and I can't seem to find a solution that works for my specific situation. Here's a mock-up of my problematic data set:
| Location | Date1 | Date2 |
|---|---|---|
| Affiliate1 | 165.45 | 148.85 |
| Affiliate2 | 485.58 | 487.58 |
The numbers represent dollar values. Here's what I want it to look like:
| Location | Date | Value |
|---|---|---|
| Affiliate1 | Date1 | 165.45 |
| Affiliate1 | Date2 | 148.85 |
| Affiliate2 | Date1 | 485.58 |
| Affiliate2 | Date2 | 487.58 |
More columns are going to be added over time, so that part needs to be dynamic. I've got a script that selects column names via information schema, but I'm struggling to incorporate it into a pivot. I don't suspect that any more affiliates will be added to the report, but if there's an easy way to make that dynamic as well I'm all ears.
