Local Administrator Group without Duplicates

[jsikora]

I am trying to create a report that generates all administrators on assets. Instead of creating a list of duplicate asset names with each administrator, I want the asset name to be listed once and administrator accounts to be seperated by commas. This is what i have so far. Im stumped, any ideas?


Select Distinct Top 1000000 tblAssets.AssetUnique,
tblUsersInGroup.AssetID,
tblAssets.Domain,
tblAssets.Description,
tblUsersInGroup.Domainname,
tblUsersInGroup.Username,
tblUsersInGroup.Lastchanged,
tsysOS.Image As icon
From tblUsersInGroup
Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique

[jsikora]

Works exactly as intended, thank you.

[Tom_P · Accepted solution]

To create a comma separated field containing the values instead of multiple lines, you can make use of the SQL statement 'STUFF' as explained in this Stackoverflow article: https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server. We added this statement to the SQL you provided.

Do note that this is a more advanced function and therefore might not be available in SQL Compact Databases.

Select Distinct Top 1000000 tblAssets.AssetUnique,
  tblUsersInGroup.AssetID,
  tblAssets.Domain,
  tblAssets.Description,
  Admins.Users
From tblUsersInGroup
  Inner Join tblAssets On tblUsersInGroup.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join (Select T1.AssetID,
    Stuff((Select ',' + T2.Username From tblUsersInGroup As T2
    Where T2.AssetID = T1.AssetID And T2.Admingroup = 1 For Xml Path('')), 1, 1,
    '') As Users
  From tblUsersInGroup As T1
  Group By T1.AssetID) As Admins On Admins.AssetID = tblUsersInGroup.AssetID
Where tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique

[briandmitchell]

Is it possible to be able to filter out certain usernames or group names?