Single Line report with H/D

[Ioannis_Simeoni]

Hello, and thank you in advance.
the system inventory report i created creates a new line for every H/D on the system, so if a systems has 3 hard drives it creates 3 lines with a same info except the Drive letter /size of the disk ext.
i need help combine all the information of a system in a single line.
plus how can i make the size number from bytes to GB ?

[TimAlex]

See 44-48 line:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  Case
    When tsysOS.OScode Like '10.0.10240%' Then '1507'
    When tsysOS.OScode Like '10.0.10586%' Then '1511'
    When tsysOS.OScode Like '10.0.14393%' Then '1607'
    When tsysOS.OScode Like '10.0.15063%' Then '1703'
    When tsysOS.OScode Like '10.0.16299%' Then '1709'
    When tsysOS.OScode Like '10.0.17134%' Then '1803'
    When tsysOS.OScode Like '10.0.17763%' Then '1809'
    When tsysOS.OScode Like '10.0.18362%' Then '1903'
    When tsysOS.OScode Like '10.0.18363%' Then '1909'
    When tsysOS.OScode Like '10.0.19041%' Then '2004'
    When tsysOS.OScode Like '10.0.19042%' Then '20H2'
    When tsysOS.OScode Like '10.0.19043%' Then '21H2'
  End As Version,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.Description,
  tblBaseBoard.Manufacturer As MBManufacturer,
  tblBaseBoard.Product,
  tblBIOS.ReleaseDate,
  tblProcessor.Name,
  tblProcessor.Caption,
  tblProcessor.MaxClockSpeed,
  tblAssets.Memory,
  Ceiling(tblOperatingsystem.TotalVisibleMemorySize / 1024) As OSMemory,
  Stuff((Select ', ' + IsNull(it.Caption, ' ') + '/' +
      IsNull(it.VideoModeDescription, ' ') From tblVideoController As it
    Where it.Caption Not Like '%dameware%' And it.Caption Not Like '%ConfigMgr%'
      And it.Caption Not Like '%radmin%' And it.Caption Not Like '%Hyper-V%' And
      it.AssetID = tblAssets.AssetID Order By it.Caption For Xml Path('')), 1,
  1, '') Videocards,
  Stuff((Select ', ' + IsNull(t1.MonitorManufacturer, ' ') + ' ' +
      IsNull(t1.MonitorModel, ' ') From tblMonitor t1
    Where t1.AssetID = tblAssets.AssetID For Xml Path('')), 1, 2, '') Monitors,
  Stuff((Select ', ' + IsNull(tblNetworkAdapter.Name, ' ')
    From tblNetworkAdapter
    Where tblAssets.AssetID = tblNetworkAdapter.AssetID And
      tblNetworkAdapter.Name Not Like '%virtual%' And
      tblNetworkAdapter.Name Not Like '%VPN%' For Xml Path('')), 1, 2,
  '') NetworkAdapters,
Stuff((Select ', ' + IsNull(tblFloppy.Model, ' ') + '(' +
      IsNull(Cast(Ceiling(tblFloppy.Size / 1024 / 1024 / 1024) As varchar(30)),
      '') + 'GB)' From tblFloppy
    Where tblAssets.AssetID = tblFloppy.AssetID And tblFloppy.Size > 0
    For Xml Path('')), 1, 2, '') HDDs,
  Stuff((Select ', ' + IsNull(tblUSBDevices.Name, ' ') From tblUSBDevices
    Where tblAssets.AssetID = tblUSBDevices.AssetID And
      tblUSBDevices.Name Not Like N'%Стандарт%' And
      tblUSBDevices.Name Not Like '%HID%' And tblUSBDevices.Name Not Like
      '%hub%' And tblUSBDevices.Name Not Like N'%концентратор%' And
      tblUSBDevices.Name Not Like N'%совместим%' And
      tblUSBDevices.Name Not Like N'%устройств%' And
      tblUSBDevices.Name Not Like '%generic%' And tblUSBDevices.Name Not Like
      N'%мышь%' And tblUSBDevices.Name Not Like N'%клавиатура%' And
      tblUSBDevices.Name Not Like '%bluetooth%' And
      tblUSBDevices.Name Not Like '%headset%' And tblUSBDevices.Name Not Like
      '%printing support%' And tblUSBDevices.Name Not Like N'%поддержка%' And
      tblUSBDevices.Name Not Like '%mouse%' And tblUSBDevices.Name Not
      Like '%keyboard%' And tblUSBDevices.Name Not Like '%USB Composite Device%'
      And tblUSBDevices.Name Not Like '%USB Pointing Device%' And
      tblUSBDevices.Name Not Like '%USB Input Device%' And
      tblUSBDevices.Name Not Like '%USB Human Interface Device%' And
      tblUSBDevices.Name Not Like '%IEEE 1284.4 compatible printer%' And
      tblUSBDevices.Name Not Like '%fax%' And tblUSBDevices.Name Not Like
      '%EWS%' And tblUSBDevices.Name Not Like '%DOT4%' And
      tblUSBDevices.Name Not Like '%LEDM%' And tblUSBDevices.Name Not Like
      N'%фильтр%' And tblUSBDevices.Name Not Like '%windows%' And
      tblUSBDevices.Name Not Like N'%дисковый%' And
      tblUSBDevices.Name Not Like '%storage%' And tblUSBDevices.Name <> '' And
      tblUSBDevices.Name Not Like '%unknown%' And tblUSBDevices.Name Not Like
      '%HP Universal Printing%' And tblUSBDevices.Name Not Like '%wia driver%'
    For Xml Path('')), 1, 2, '') USBs,
  tblAssets.Lastseen
From tblAssets
  Left Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
  Left Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Assettype = -1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName