Split information from two columns in the same row

[borgoncete88]

Hi all,

I was trying to make a report looking for the AD groups an user is member of. In the end I got the data I needed, but i'd be nicer if I can join all the groups in the same row for each user. Like below:

Users | Groups

AdUser1 | AdGroup1, AdGroup2, AdGroup3, etc...

This is my current code:

Select Top 1000000 tblADusers.Username,
tblADusers.Displayname,
Case tblADGroups.GroupType
When -2147483646 Then Concat(tblADGroups.Name, ' (Security - Global)')
When -2147483644 Then Concat(tblADGroups.Name, ' (Security - Local)')
When -2147483643 Then Concat(tblADGroups.Name, ' (Built-in)')
When -2147483640 Then Concat(tblADGroups.Name, ' (Security - Universal)')
When 2 Then Concat(tblADGroups.Name, ' (Distribution - Global)')
When 4 Then Concat(tblADGroups.Name, ' (Distribution - Local)')
When 8 Then Concat(tblADGroups.Name, ' (Distribution - Universal)')
End As [AD Group Name (Type)]
From tblADusers
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Order By tblADusers.Userdomain,
tblADusers.Username

Any help would be thankful! Kind regards,

Borja GC

[Mister_Nobody]

* nested membership is not included

[Mister_Nobody · Accepted solution]

Try this

Select Top 1000000 adu.Username,
  adu.userdomain,
  Stuff((Select ', ' + tblADGroups.Name As [text()]
    From tblADMembership Inner Join tblADGroups On
          tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID Inner Join
      tblADusers On tblADMembership.ChildAdObjectID = tblADusers.ADObjectID
    Where tblADusers.Username = adu.Username And tblADusers.Userdomain =
      adu.userdomain For Xml Path('')), 1, 2, '') Groups
From tblADMembership As admem
  Inner Join tblADGroups On admem.ParentAdObjectID = tblADGroups.ADObjectID
  Inner Join tblADusers As adu On admem.ChildAdObjectID = adu.ADObjectID
Group By adu.Username,
  adu.userdomain

[borgoncete88]

Hi @Mister_Nobody ,

It worked perfectly as I wanted. You're the best! Thank you so much for your help 🙂