I have a sql table that contains user names and the agencies they are restricted to:
I want to return data from that table looking like this:
Dave T200, HHF001
So far my sproc looks like this. However, it brings back all of the agencies into one user name, like this:
Dave T200, HHF001, 04042, HCF200
What is the proper way to accomplish this? Thanks
@userID INT = 42
SET @userAgencyList = ''
SET @agencyList = ''
@userName = u.DisplayName,
@userAgencyList = @userAgencyList + a.Identifier + ' '
from cad.UserAgencyRestriction uar
left join adm.[User] u
on u.UserID = uar.UserID
left join adm.Agency a
on a.AgencyID = uar.AgencyID