Seperate results from rows

[jbenco]

Hello community,

I'm trying to make an overview to check if certain software is installed and im stuck.
The overview i am trying to create should look like this:

Computer name | 7-zip    | Skype     |   Visio                    |
-------------------------------------------------------------------
PC1           |7-zip 3.4 | Skype 6.1 | Microsoft office visio 2010|
PC2           |-         | Skype 6.2 | Microsoft office visio 2007|
PC3           |7-zip 3.2 | -         | Microsoft office visio 2010|

This table is an example.

I mannaged to get the collumns next to each other en get the computer names and links to its stats.
But then the problem is the table does not show up as above but like this:

Computer name | 7-zip                      | Skype                      |   Visio                    |
------------------------------------------------------------------------------------------------------
PC1           |7-zip 3.4                   | 7-zip 3.4                  | 7-zip 3.4                  |
PC1           |Skype 6.1                   | Skype 6.1                  | Skype 6.1                  |
PC1           |Microsoft office visio 2010 | Microsoft office visio 2010| Microsoft office visio 2010|

Below is the query wich i used to get this result and i have no clue to fix this.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName As [7-zip],
  Reader.softwareName As [Adobe Reader]
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tblSoftwareUni Reader On tblSoftware.softID = Reader.SoftID
Where (tblSoftwareUni.softwareName Like '7-Zip%') Or
  (Reader.softwareName Like 'Adobe Reader%')
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName,
  Reader.softwareName

The table Reader is a copy of tblSoftwareUni
I hope sombody is able to help me 🙂
Thanks in advance,

Jeroen

[Hemoco]

Not without complicated workarounds, no. SQL was designed to display table records as lines, not columns.

[jbenco]

Thank you very much!
This helped me out.

Altough i now have an overview of importand software installed for each computer in this network it is still showing multiple results wich is kind of obvious.

The problem is when 1 computer has for example adobe reader X and adobe reader IX installed the report shows multiple results for the same computer.
Is there a way to check in SQL if there are more then 1 results from any subquery then merge then so it displays both in 1 column?

[Hemoco]

Sample report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
SubQuery1.softwareName As [7-Zip],
SubQuery2.softwareName As [Adobe Reader]
From tblAssets
Left Join (Select Top 1000000 tblAssets.AssetID,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '7-Zip%') SubQuery1
On SubQuery1.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Adobe Reader%') SubQuery2
On SubQuery2.AssetID = tblAssets.AssetID