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