I had a similar issue to this post here:
I followed the steps but didn’t give me the solution I needed.
I want to find the Invoice numbers linked to PO lines. And since there can be multiple invoices for a PO line, I would like the results to be seen in one line instead of multiple lines.
Please help if you can fix this.
select [PORel].[PONum] as [PORel_PONum], [PORel].[POLine] as [PORel_POLine], [PORel].[PORelNum] as [PORel_PORelNum], [PORel].[RelQty] as [PORel_RelQty], [PORel].[ReceivedQty] as [PORel_ReceivedQty], [PODetail].[PartNum] as [PODetail_PartNum], [PODetail].[LineDesc] as [PODetail_LineDesc], (REVERSE(stuff(REVERSE(REPLACE(REPLACE(((select (cast(APInvDtl.InvoiceNum as varchar) + ', ') as [Calculated_Invoices] from Erp.APInvDtl as APInvDtl1 where (APInvDtl1.PONum = APInvDtl.PONum FOR XML PATH('')))) , '</Calculated_Invoices>',''),'<Calculated_Invoices>','')), 1, 1, ''))) as [Calculated_Invoices_] from Erp.PODetail as PODetail inner join Erp.PORel as PORel on PODetail.Company = PORel.Company and PODetail.PONUM = PORel.PONum and PODetail.POLine = PORel.POLine and ( PORel.OpenRelease = 1 and PORel.RelQty = PORel.ReceivedQty and PORel.PONum = 108661 ) inner join Erp.APInvDtl as APInvDtl on APInvDtl.Company = PORel.Company and APInvDtl.PONum = PORel.PONum and APInvDtl.POLine = PORel.POLine and APInvDtl.PORelNum = PORel.PORelNum