Dell PC list with attached Monitors

[J-M]

Hi,

I am using SQL Compact Database. I would like to get a listing of all my PCs with their attached monitors. Some of my pcs have up to 3 monitors attached. The problem is that my report is breaking out each attached monitor to a separate line. I would like to have kind of like a grouping of my monitors attached to to the pc. kinda like.....

PC Name | Monitor | Serial Number
PC1 --- Dell 2214
- - ----- Dell 2214

PC2 --- Dell 2215
- - ----- Dell 2214


Here is my current SQL...

Select Distinct Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssets.AssetUnique,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model,
tblAssets.AssetName,
tblMonitor.MonitorModel,
tblMonitor.SerialNumber
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblMonitor On tblAssets.AssetID = tblMonitor.AssetID
Where tblAssetCustom.State = 1

How can I get the desired results?

[Susan_A · Accepted solution]

It is expected behavior for there to be one line per monitor and for the computer name to be repeated in each line. The Lansweeper report builder is a standard SQL editor. SQL displays table records (e.g. monitors) as lines, not columns. We posted a sample report here that uses the Stuff function to get everything on one line for a single computer, but this report won't work in a SQL Compact database.