This commonly achieved by using Transpose and Cross Tab, but this approach converts the values into the V_WString data type.The next challenge is converting the count values back into a numeric data type.
The difficulty is that the field names are dynamic, so we cannot simply use a Select tool and manually change the data types.
Additionally, we need to preserve the original field order.
Hence, i built this macro to help.
Warning: This macro also behaves like the Select tool when converting data types, which means it can run into the same issues.
For example, converting the string "1,000" to an integer will result in 1, because the conversion stops at the comma.
Config:
Input:
Output:
