New to CMPivot with the SCCM 1906 update is the Join operator.
Limitations
- The join column is always the Device field.
- Maximum of 5 joins per query.
- Maximum of 64 combined columns.
Example
ComputerSystem
| project Device, Manufacturer, Model
| join (OperatingSystem | project Device, OSVersion=Caption)
https://docs.microsoft.com/en-us/sccm/core/servers/manage/cmpivot#bkmk_cmpivot_joins