Microsoft Office Report - Help

[Cam]

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

[Cam · Accepted solution]

Thank you both. I guess I need to combine the 'sql stuff xml path' as shown by RC62N with the report provided by Susan. I'll try and post the final code once I have a chance to get it working.

[Susan_A]

You should not be using license key information (tblSerialnumber) to report on software installations. Software installation data is stored in tblSoftware/tblSoftwareUni.

Please have a look at this knowledge base article, which explains the difference between software installations and software license keys, and build a report based on tblSoftware/tblSoftwareUni instead. A sample report can be found here. Your current report will not accurately reflect which computers have Office installed.

[RCorbeil]

Google up "sql stuff xml path".

My first experiment with it using your question as the challenge:

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

Product ID and key concatenated just because I wanted to see them there.