Condensing a Select's several returned characters into a single string

Question

Simply put, I have a select that will return multiple single characters, and thus won't work. Is there any way to bunch all the single characters into a single returnable string?

My current slow and ugly solution:

,'('+(Select Left(max(AE_D1),1) 
        FROM ACCESS_EVENTS
      WHERE LEFT(AE_D1,1) like 'W'
        AND replace(HR.firstname,' ','')+' '+replace(HR.lastname, ' ','') LIKE Left(AE_D2,len(replace(HR.firstname,' ','')+' '+replace(HR.lastname, ' ','')))) +')'
+'('+(Select Left(max(AE_D1),1) 
        FROM ACCESS_EVENTS
       WHERE LEFT(AE_D1,1) like 'M'
         AND replace(HR.firstname,' ','')+' '+replace(HR.lastname, ' ','') LIKE Left(AE_D2,len(replace(HR.firstname,' ','')+' '+replace(HR.lastname, ' ','')))) +')'

. . . Repeat until all cases are covered . . .

EDIT - Extra information: Each 'W' or 'M' is the first character of an access event's name, related to where the event took place. There are about 9 different event characters to cover. The point of the query is to show when a person has events from more than one area associated to their name. The problem now is just to display it cleanly.

Answer 1

Could an indexed view work for your desired data?

http://technet.microsoft.com/en-us/library/cc917715.aspx#XSLTsection124121120120

Answer 2

I think you want to read this.