Trying to create a BAQ that lists sales by salesperson. Problem is SalesRepList can hold multiple. Help!

Epicor ERP 10
13

With MSSQL 2016+ you can cross apply string_split like so (remove ordinal for better perf or not 2022+)

SELECT 
    InvcHead.Company AS InvcHead_Company,
    InvcHead.InvoiceNum AS InvcHead_InvoiceNum,
    s.value AS Split_SalesRep,
    s.ordinal AS SalesRep_Order -- mssql2022+
FROM InvcHead
CROSS APPLY STRING_SPLIT(InvcHead.SalesRepList, '~', 1) s
ORDER BY InvcHead.InvoiceNum, s.ordinal;
InvcHead_Company InvcHead_InvoiceNum Split_SalesRep SalesRep_Order
111111 1001 RepA 1
111111 1001 RepB 2
111111 1001 RepC 3
111111 1002 RepX 1
111111 1002 RepY 2
111111 1003 RepM 1
111111 1003 RepN 2
111111 1003 RepO 3
111111 1003 RepP 4
1 Like