@v-monli-msft: No. This is not "epxected" behaviour, and you won't convince me otherwise. DisplayName = F(X) indicates that it is a function of X. So, when X changes, so must F(X).
This completely ruins the possiblity to have dynamic column names. The column name isn't the same as the DisplayName which can, and should be anything the user wants it to be, at any time. The actual CTE i'm reading has the columns numbered, where 0 is the current year and week. The column names are integers, which are not the same as display names. What the lookup does is simply convert those integers to DisplayNames, but if I'd like the data of a column, It would be absolutely logical to use the column name as specified in the CTE, not the display name.
The solution is very simple - two properties. One Name for the column which is static, and DisplayName which is dynamic and anything the user wants to map it to and display.
I just want a workaround for this, as now I will be forced, every week, to manually update 52 column names for a rolling year.
As a novice devloper, even I can understand how ludicrous this is. I'm thinking of going back to developing in C# and .NET, not that I'm an epxert at it, but because the time I'm spending doing all these workarounds and flows to get a single table from SQL is a complete waste of my time, and might as well be spent learning to code on a more capable platform.
I want to believe it's easy and that I can do these simple tasks, but to everything the rebuttal is "post in suggestion forum", instead of acknowledging the issue and providing a timeline for it.
