Concatenating Values in SQL Server using a CTE -

i have been trying concatenate values particular row. have query:

select      tblcontacts_2.contactid,      (coalesce (tblsites_1.sitenm, '')       + coalesce (' / ' + tblsites_1.sitenmalt, '')       + ' ' + coalesce (tblsites_1.addr1, '') + ' '       + coalesce (tblsites_1.zipcodeid, '') ) sitenameaddr,      tblsites_1.siteid         dbo.tblcontacts tblcontacts_2  inner join      dbo.tjncsitecontacts tjncsitecontacts_1 on tblcontacts_2.contactid = tjncsitecontacts_1.contactid  inner join      dbo.tblsites tblsites_1 on tblsites_1.siteid = tjncsitecontacts_1.siteid  

the output

contactid   sitenameaddr                              siteid --------------------------------------------------------------  329        twin parks southwest / twin parks south...   1 1788        twin parks southwest / twin parks s....      1 

what want is, output of format sitenameaddrs 1 contact id concatenated below count of site ids associated with:

contactid    sitenameaddr                                     countsite ----------------------------------------------------------------------- 321          riverside park /  3333 broadway 10035, urban...      8 322          westview 625 main st 10044                           1 

the problem is, following code fails while doing -

select      tblcontacts_2.contactid,      dbo.removelastchar(dbo.strconcat(coalesce (tblsites_1.sitenm, '')                                      + coalesce (' / ' +                                      tblsites_1.sitenmalt, '')                                      + ' ' + coalesce (tblsites_1.addr1, '')                                      + ' '                                      + coalesce (tblsites_1.zipcodeid, '') +                                      ', '))                              sitenameaddr,      count(tblsites_1.siteid) countsite         dbo.tblcontacts tblcontacts_2  inner join      dbo.tjncsitecontacts tjncsitecontacts_1 on tblcontacts_2.contactid = tjncsitecontacts_1.contactid  inner join      dbo.tblsites tblsites_1 on tblsites_1.siteid = tjncsitecontacts_1.siteid  group      tblcontacts_2.contactid   

error:

a .net framework error occurred during execution of user-defined routine or aggregate "strconcat":
system.data.sqlserver.truncationexception: trying convert return value or output parameter of size 8086 bytes t-sql type smaller size limit of 8000 bytes.

this because 1 of contact ids has 92 sites associated , concatenated string becomes large. also, dbo.removelastchar , dbo.strconcat scalar functions can return nvarchar , hence can't use ntext in them. type-casting of data-type not working either.

please let me know alternative this. concatenated string picked vba code in access