List computer software in a single row

[laurentiun]

Hello,

Is there any way to list the software installed on a computer on a single row? I want to avoid having duplicate "computers" for each software installed... I end up having thousands of lines instead one line per each computer with all software installed.

I would like to have something like: AssetName | software1, software2, software3, etc.

Or a single big row like
AssetName | softwarename1 | IP | etc
--space-- softwarename2
--space-- softwarename3


Thank you

[Andy_Sismey]

Hi,

This should work for the option 2 you described, a little slow but give this a go :

Select Top 1000000 Case
    When Row_Number() Over (Partition By tblassets.AssetName Order By
      Query1.softwareName) = 1 Then tblassets.AssetName
    Else Null
  End As 'Name',
  Query1.softwareName
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join (Select tblSoftwareUni.softwareName,
      tblSoftware.AssetID
    From lansweeperdb.dbo.tblSoftware
      Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID) Query1 On Query1.AssetID = tblassets.AssetID
Where tblassetcustom.State = 1