As this zombie thread still maintains its relevance, I am back yet again. We are validating 10.2.700.14 and the original queries are not working, including @josecgomez 's original example. The latest version of SQL server coupled with the epicor version calls various errors. The solution is to use the new STRING_AGG function which works very well and simplifies the original. Note this is not backwards compatible. A small note, if you use use STRING_AGG on a current version it may throw an error as it is limited to returning only 8kb. The solution is to cast it into nvarchar(MAX).
To be crystal clear, If your SQL version is not 2017+ you will not be able to use STRING_AGG
Attaching an updated CustOrder query modified by @LoganS
select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
(STRING_AGG(cast(OrderHed.OrderNum as nvarchar(MAX)), ', ')) as [Calculated_Aggregate]
from Erp.Customer as Customer
inner join Erp.OrderHed as OrderHed on
Customer.Company = OrderHed.Company
and Customer.CustNum = OrderHed.CustNum
group by [Customer].[CustID],
[Customer].[Name]
CustOrder700.baq (23.7 KB)