Multiple software on one report

[JTZRFB]

I did a search, but just not pulling up what I'm looking for. I'm wanting to to have one report with multiple columns. One for Firefox and one for Chrome. Right now Assets show up on two lines. One will have Chrome listed and the other line Firefox.

Ideally the columns should be
Asset Name, Software Name Firefox, Firefox Version, Software Name Chrome, Chrome Version

If you can point to posts that has accomplished it would be greatly appreciated. Thanks


Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblSoftwareUni.softwareName Like '%Chrome%' Or
tblSoftwareUni.softwareName Like '%Firefox%') And tblAssetCustom.State = 1
Order By tblAssets.AssetID

[RCorbeil]

Try this. It's based on the standard base query.

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Chrome.OneOrAll AS ChromeWhoFor,
  Chrome.softwareName AS ChromeSoftware,
  Chrome.softwareVersion AS ChromeVersion,
  Firefox.OneOrAll As FirefoxWhoFor,
  Firefox.softwareName As FirefoxSoftware,
  Firefox.softwareVersion As FirefoxVersion
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select
               tblSoftware.AssetID,
               Case When tblSoftware.CurrentUser = 1 Then 'User' Else 'Global' End As OneOrAll,
               tblSoftwareUni.softwareName,
               tblSoftware.softwareVersion
             From
               tblSoftware
               Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
             Where
               tblSoftwareUni.softwareName Like '%Google Chrome%'
               And tblSoftwareUni.softwareName Not Like '%Google Chrome Frame%') As Chrome On Chrome.AssetID = tblAssets.AssetID
  Left Join (Select
               tblSoftware.AssetID,
               Case When tblSoftware.CurrentUser = 1 Then 'User' Else 'Global' End As OneOrAll,
               tblSoftwareUni.softwareName,
               tblSoftware.softwareVersion
             From
               tblSoftware
               Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
             Where
               tblSoftwareUni.softwareName Like '%Firefox%'
               And tblSoftwareUni.SoftwarePublisher Like '%Mozilla%') As Firefox On Firefox.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1