I have a transactional table with 100+ columns and each row is uniquely identified by an id column. For a data export requirement, we need to make export the table flat - aka, id/column_name/column_value format. the number of columns are dynamic in nature. By this, I mean : there could be 10 additional columns in near future. Some columns might get dropped as well. This is another reason for exporting as a name/value pair.
A dynamic pivoting might be the answer - I tried using dynamic pivoting using xmlpath/cross apply and made things complex enough to get a cross joined resultset. Here is the sample schema (input/output) with sample data:
Any help is highly appreciated.