My query's output needs the Entity.Name value combined into one column called ExtAccessLevelID, separated by commas. Currently, every Entity.Name value that is separate appears in a separate row, giving me multiple results for the same person.
I've been working at getting the desired result with FOR XML, but am unsure how to get it to work with 5 tables. I've included my most basic query as a start.
So the end result should be: Smith John xxx xxxx xxxx name1,name2,name4
Any help / direction would be appreciated!
Replace(Cardholder.Guid, '-', '') As 'ExtUserID',
Cardholder.ExtendedGrantTime As 'ExtendedOpeningTime',
Cardholder.ExpirationDate As 'UserExpiration.ExpDate',
Credential.UniqueID As 'WeigandCode',
Entity.Name As 'ExtAccessLevelIDList',
Case When Credential.Status > 0 Then '8' Else Null End As 'Action'
Cardholder Left Outer Join
Credential On Credential.Cardholder = Cardholder.Guid Inner Join
CardholderMembership On Cardholder.Guid = CardholderMembership.GuidMember
CardholderGroup On CardholderMembership.GuidGroup = CardholderGroup.Guid
Entity On CardholderGroup.Guid = Entity.Guid
Entity.Type = 8