→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎05-13-2015 06:02 PM - last edited on ‎02-23-2023 01:44 PM by Mercedes_O
Hey All,
I have the following report for Computers with Microsoft Office:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblSerialnumber.Product,
tblSerialnumber.ProductID,
tblSerialnumber.ProductKey,
tsysOS.Image As icon
From tblAssets
Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblSerialnumber.Product Like '%microsoft%office%' And
tblAssetCustom.State = 1
Order By tblAssets.AssetUnique,
tblSerialnumber.Product
Which results with something like the picture shown below. My issue is that I have tons of duplicate computer names in the report (due to them having multiple versions of Office installed). Is there any ways to combine the duplicate computer names and comma separate the Product column? I don't even need the ProductID or ProductKey columns (so I can remove those).
Thanks,
Cam
Solved! Go to Solution.
‎05-27-2015 05:00 PM
‎05-27-2015 05:00 PM
‎05-15-2015 03:58 PM
‎05-13-2015 07:46 PM
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
Stuff( ( SELECT
', ' + tblSerialnumber.Product
FROM
tblSerialnumber
WHERE
tblAssets.AssetID = tblSerialnumber.AssetID
AND tblSerialnumber.Product Like '%microsoft%office%'
ORDER BY
tblSerialnumber.Product
FOR Xml Path('')
), 1, 2, '') AS Products,
tsysOS.Image As icon
FROM
tblAssets
INNER JOIN tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysOS On tblAssets.OScode = tsysOS.OScode
WHERE
tblAssetCustom.State = 1
ORDER BY
tblAssets.AssetUnique
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now