Encryptable Volume Question

[smozie]

Trying to see if I can condense some of the information in my report. Right now each individual drive has its own row but I want the drives to be listed/grouped together so that I can get an accurate number for how many hosts need to be encrypted as opposed to the total drives that needs to be encrypted.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblEncryptableVolume.DriveLetter,
  Case When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON' Else 'UNKNOWN'
  End As ProtectionStatus
From tblEncryptableVolume
  Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysIPLocations On tblAssets.LocationID =
    tsysIPLocations.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblState.Statename = 'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName

[Bruce_B]

Condensing multiple rows per asset into a single row, when there is non-distinct data is not a straight-forward process and would require some advanced SQL. You may be able to achieve this using STUFF (SQL Server only).