From what I just experienced, it seems that the column headers that are being pivoted whilst calling CrossTable are cast to a "hard" string. What I mean by hard here is that the common duality of integer and string appears to be suspended. In the code below, this would be the case for Month (which looks like this: 202005)
CrossTable(Month, FxRate, 3)
FROM [$(vDirPath)\ManualInputs.xlsx] (ooxml, embedded labels, table is FxGbp)
I made some tests and things along the line of
NUM(Month, '#') or Month * 1
fail. On the other hand
Month & '_' works and returns 202005_
I have to join this data further down the script with another field that under normal conditions is treated as an integer. It comes from SQL by the way, but it keeps being treated as integer also if I explicitly cast it as NVARCHAR, which I do either way
LEFT(CAST(TxDateSerial AS NVARCHAR), 6) AS month_serial
20200514 -> 202005
The only way I managed to have the join work thus far is by "forcing" the SQL field into a string by concatenating a '_' after the LEFT(), but I really don't like this kind of bodges.
Is there a way to have the CrossTable month treated as an integer/dual?