
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-12-2016 04:00 PM
I have the following report.
As most of the machines have 2 network adaptors, 2 video adaptors and 3 or more IDE controllers I'm getting a line for each combination of those, so
net adaptor 1 with VGA adaptor 1 and IED adaptor 1
net adaptor 2 with VGA adaptor 1 and IDE adaptor 1
net adaptor 1 with VGA adaptor 2 and IDE adaptor 1
I understand that there is no way to completely flatten this to a single line but I'd like to understand if there is a way to more intelligently join these bits of data to reduce the total number of data output?
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Processor,
tblAssets.Memory,
tblAssets.IPAddress,
tblVideoController.Caption,
tblNetworkAdapter.Name,
tblIDEController.Caption As Caption1
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblVideoController
On tblAssets.AssetID = tblVideoController.AssetID
Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
Inner Join tblIDEController On tblAssets.AssetID = tblIDEController.AssetID
As most of the machines have 2 network adaptors, 2 video adaptors and 3 or more IDE controllers I'm getting a line for each combination of those, so
net adaptor 1 with VGA adaptor 1 and IED adaptor 1
net adaptor 2 with VGA adaptor 1 and IDE adaptor 1
net adaptor 1 with VGA adaptor 2 and IDE adaptor 1
I understand that there is no way to completely flatten this to a single line but I'd like to understand if there is a way to more intelligently join these bits of data to reduce the total number of data output?
Labels:
- Labels:
-
Report Center
1 REPLY 1

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-23-2016 04:56 PM
As you mentioned getting multiple rows is expected when there is more than one line of data for a field in a table. By simply linking the tables differently you will most likely not get any changes as you are still calling the same tables.
Howerver, There are ways to combine multiple rows into a single row. These do require having advanced knowledge of SQL. You can find information on how to do this online such as here and here.
Howerver, There are ways to combine multiple rows into a single row. These do require having advanced knowledge of SQL. You can find information on how to do this online such as here and here.
