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

SCCM 1906 introduced some cool stuff relating to CMPivot and one of my favourites is the standalone installer for CMPivot so you can use without having to access the SCCM admin console (the installer is available in the Tools folder under your main install folder e.g. C:\Program Files\Microsoft Configuration Manager\tools.

Tip – there is a dropdown box in the top left of the CMPivot window that allows you to browse and select the collection to work CMPivot against.