Related assets listed in one field

[ppoppe]

Hey folks,

we use the asset relation possibility where often.
We connected many assets with others. For example we have one parent asset and about 10 other child assets are related to it.
Now we want to have a report where there is one line for every parent asset with some details and one field in that line where all child assets or user relations are listed.
We want to avoid several lines for the same parent asset only because there are 3 or 4 user relations or other child assets.

I hope someone of you has a solution for it.

Thanks in advance!

[Daniel_B · Accepted solution]

Giving out several results in one row is not really supported by SQL, which we use to generate reports. If your database is running on SQL server Express or higher edition, you can accomplish this with a query like the following example:

Select 
  tParent.Assetname, 
  Stuff(( Select ', ' + tConnected.AssetName 
          From tblassetRelations 
          Inner Join tblAssets tConnected 
		  On tblAssetRelations.ChildAssetID = tConnected.AssetID 
		  Where tParent.AssetID = tblAssetRelations.ParentAssetID
		  Order By tConnected.AssetName 
  For xml path('')),1,1,'')
From tblassets tParent
Order By tParent.AssetName