lol.. was just thinking the same thing…
Here is working example. Your CTE would be a bit different.
="
with NotePad as
(
select key2, key3, key4, key5,Company ,STRING_AGG(character01, CHAR(13) + CHAR(10)) as Key5Notes
from KineticPilot.ice.ud01 where CHECKBOX02 = 1
group by key2, key3, key4, key5,Company
)
SELECT
PartNotes.key5notes as PartNotes,
OrderDtlNotes.Key5Notes as OrderDtlNote,
custNoteNotes.key5notes as CustNote,
OrderHdrNotes.key5Notes as OrderHdrNote,
T1.Company,T1.LegalNumber,T1.PackNum,CAST( T1.CustNum as nvarchar ) as CustNum,T1.ShipComment,T1.ShipDate,T1.Calc_BilContct,T1.Calc_BillToAddress,T1.Calc_SoldToAddress,T1.Calc_Contct,T1.Calc_CustPartOpts,T1.Calc_FOBDescription,T1.Calc_LegalNum,T1.Calc_NumRecordPerPage,T1.Calc_NumTotalParts,T1.Calc_SalesRepName,T1.Calc_ShipToAddress,T1.Calc_ShipViaDescription,T1.Calc_stPhone,T1.Calc_CusPhone,T1.Calc_stFax,T1.Calc_EMailAddress,T1.Calc_FaxNum,T1.Calc_SEmailAddr,T1.Calc_CarrierDesc,T1.Calc_ShipViaSCAC,T1.Calc_CarrierSCAC, T2.PackLine,T2.Discount,T2.ExtPrice,T2.HeaderShipComment,T2.LineDesc,T2.OrderLine,T2.OrderNum,T2.OrderRelNum,T2.PartNum,T2.RevisionNum,T2.ShipComment as ShipDtl_ShipComment,T2.UnitPrice,T2.XPartNum,T2.XRevisionNum,T2.Calc_DspBackOrdQty,T2.Calc_DspBackOrdQtyUom,T2.Calc_DspLabDur,T2.Calc_DspLabMod,T2.Calc_DspLineDesc,T2.Calc_DspLineShpQty,T2.Calc_DspLineShpQtyUom,T2.Calc_DspMatDur,T2.Calc_DspMatMod,T2.Calc_DspMiscDur,T2.Calc_DspMiscMod,T2.Calc_DspPlannedQty,T2.Calc_DspPlannedQtyUom,T2.Calc_DspSerialNumber,T2.Calc_DspShipRouting,T2.Calc_DspSubShipTo,T2.Calc_GetNextLegalNum,T2.Calc_LegalText,T2.Calc_LinChangd,T2.Calc_NextLegalNumID,T2.Calc_NumLineByInv,T2.Calc_OrdRelRef,T2.Calc_POLine,T2.Calc_SalesRepName as ShipDtl_Calc_SalesRepName,T2.Calc_SerialNumLabl,T2.Calc_TotalLineCost,T2.Calc_TotalTax,T2.Calc_HidePackLine,T2.OrderLine_KitFlag,T2.OrderLine_KitParentLine,T2.OrderLine_KitPrintCompsPS,T2.OrderLine_DisplaySeq,T2.OrderLine_KitShipComplete,T2.Calc_MarkForAddress,T2.OrderNum_PONum,T2.PartNum_PartDescription,T2.PCID,T2.Calc_CommodityCode, T2.OrderRelNum_ReqDate ReleaseShipDate, T2.OrderRelNum_NeedByDate ReleaseNeedByDate,
FROM ShipHead_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN ShipDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.PackNum = T2.PackNum
Join NotePad as PartNotes on T2.PartNum = PartNotes.Key2 and PartNotes.Key5 = 'Part'
Left Join NotePad as OrderDtlNotes on T2.OrderNum = OrderDtlNotes.Key2 and T2.OrderLine = OrderDtlNotes.Key3 and T2.Company = OrderDtlNotes.Company and OrderDtlNotes.Key5 = 'OrderDtl'
left join Notepad as OrderHdrNotes on T2.OrderNum = OrderHdrNotes.Key2 and T2.Company = OrderHdrNotes.Company and OrderHdrNotes.Key5 = 'OrderHed'
left join Notepad as CustNoteNotes on T2.CustNum = CustNoteNotes.Key2 and T2.Company = CustNoteNotes.Company and CustNoteNotes.Key5 = 'Customer'
"
