REPORTS - Join data from three different columns into the same one

[borgoncete88]

Hi all,

I'd like to join the data from three different columns in a report, this fields are amount of RAM, DDR type and memory speed in MHZ. I've got the parameters and the report shows them well, but as I have it in the next column about HDD info (copied SQL code from another guy here in the forum), I guess is possible to join the RAM data into the same column, but I tried for hours in SQL and always gives me an error. I'm not an expertise in SQL, but I'm pretty sure it's not difficult to do it. I'll paste you my query and I hope you guys could help me to solve my question:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As NOMBRE,
tblAssets.Firstseen As DETECTADO,
tblAssets.Lastseen As [ULT. VISTO],
tblAssets.Username As USUARIO,
tblAssetCustom.Department As DPTO,
tblAssetCustom.Building As PLANTA,
tsysOS.OSname As OS,
tblAssets.Version As [VERSION OS],
tblAssets.IPAddress As IP,
tblAssets.Mac As MAC,
tblBaseBoard.Manufacturer As MARCA,
tblAssetCustom.Model As MODELO,
tblBaseBoard.Product As [PLACA BASE],
tblAssetCustom.Serialnumber As [N/S],
tblAssets.Processor As CPU,
tblProcessor.NumberOfCores As CORES,
tblBIOS.ReleaseDate As [FECHA BIOS],
Cast(tblAssets.Memory / 1024 As numeric) As [RAM GB],
TsysMemorytypes.MemoryName As [TIPO DDR],
tblPhysicalMemory.Speed As [RAM MHZ],
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, '') [DISCO DURO],
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, '') GRAFICA,
Ceiling(tblVideoController.AdapterRAM / 1024 / 1024 / 1024) As [VIDEO RAM GB],
Stuff((Select ', ' + IsNull(t1.MonitorManufacturer, ' ') + ' ' +
IsNull(t1.MonitorModel, ' ') From tblMonitor t1
Where t1.AssetID = tblAssets.AssetID For Xml Path('')), 1, 2, '') MONITOR,
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, '') RED,
tblAssetCustom.Warrantydate As GARANTIA,
tblAssetCustom.Custom3 As [EXT. GARANTIA],
tblAssetCustom.PurchaseDate As [FECHA COMPRA],
tblAssets.LsAgentVersion As [VERSION AGENTE LS],
tblAssetCustom.Custom1 As [HISTORIAL ULT. USUARIO],
tblAssetCustom.Custom2 As OBSERVACIONES,
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, '') [CONEXIONES USB]
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
Inner Join tblPhysicalMemory On tblAssets.AssetID = tblPhysicalMemory.AssetID
Inner Join TsysMemorytypes On tblPhysicalMemory.MemoryType =
TsysMemorytypes.Memorytype
Inner Join tblVideoController On
tblAssets.AssetID = tblVideoController.AssetID
Where tblAssets.Assettype = -1 And tblAssetCustom.State = 1
Order By NOMBRE

Thank you in advance.

[borgoncete88]

Hi guys,

I can finally solve it using a concat

Concat(Cast(tblAssets.Memory / 1024 As numeric), 'GB ',
TsysMemorytypes.MemoryName, ' - ', tblPhysicalMemory.Speed, ' MHz') As RAM,
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, '') [DISCO DURO],

Cheers.