WITH singleInventory (guid_machine, entry, description, maxd) as
(
    SELECT guid_machine, entry, LTRIM(RTRIM(description)) as description, max(datetime) as maxd
    FROM   bms.dbo.inventory_nodeproperties
    group by guid_machine, prop_id, entry, description
),
inventory (guid_machine, pcmodell, video, harddrive, harddrive_cap, nic, monitor_name, memory, serialnr) as
(
    select distinct guid_machine, [Computer Brand Name] as pcmodell, [Video Chipset] as video, [Drive Model] as harddrive, [Drive Capacity] as harddrive_cap, [Network Card] as nic,

         Stuff(
    (
    SELECT ',' + description
    FROM singleInventory si
    where si.entry = 'Monitor Name (Manuf)' and si.guid_machine = piv.guid_machine
    FOR XML PATH('')
    ), 1, 1, '') AS monitor_name, [Total Memory Size] as memory, [Product Serial Number] as serialnr
    from
    (
       select guid_machine, entry, description
       from singleInventory
    ) d
    pivot
    (
       max(description)
       for entry in ([Computer Brand Name], [Video Chipset], [Drive Model], [Drive Capacity], [Monitor Name (Manuf)], [Network Card], [Total Memory Size], [Product Serial Number])
    ) piv
)

SELECT m.guid,
'Production' as deplStateID,
'Operational' as incidentStateID,
m.displayname AS RechnerID,
m.primary_mac,
/*m.maclist,*/
m.primary_ip,
/*m.iplist,*/
m.osverstring,
/*m.options,*/
m.lastuser,
m.domain,
/*'Last user: '+m.lastuser+char(13)+char(10)+' Domain: '+m.domain as description,*/
m.cpudesc,
/*m.cpuspeed,*/
/*m.totalmem,*/
/*m.drivecsize*/
m.arch,
/*m.energy_scheme*/
inventory.pcmodell,
inventory.video,
inventory.harddrive,
inventory.harddrive_cap,
inventory.nic,
inventory.monitor_name,
inventory.memory,
inventory.serialnr
FROM  bms.dbo.machine m
LEFT JOIN inventory on m.guid = inventory.guid_machine;
