i have data coming from a large query that needs to be transposed. Piviot might not work in this instance, and either way i havent been able to get it to work. This cannot be done in SSRS, it has to be straight from the DB, output as a table for another application to use.
this result is currently generated by dynamic sql, as the USERCURR columns is dynamic based on the calling app. lets say there are columns USERCURR1 through USERCURR10. Not all columns will have data.
The Original table will have some static columns that need to remain, and then instead of the USERCURR columns, they are replaced with two columns, BUCKETNAME and BUCKETAMT. For each row, it get SPLIT into multiple rows based on the number of USERCURR columns. I have attached a spreadsheet which clearly explains the need.
Ideally, if the USERCURR column is null or 0 we would not include it, but thats ok as it could be cleaned up later