What's going on here?

[Matthew_Morgan]

I’m trying to attempt was has been discussed here

Concatenate multiple rows into one row, with criteria

I have created a BAQ to list multiple PO Numbers in one Row, separated by a comma as follows: [image] I created this as follows: [image] [image] [image] SQL Query Phrase: select [OrderDtl].[OrderNum] as [OrderDtl_OrderNum], [OrderDtl].[PartNum] as [OrderDtl_PartNum], [OrderDtl].[LineDesc] as [OrderDtl_LineDesc], (REPLACE(REPLACE(((select (CAST(PODetail.PONUM AS VARCHAR) + ‘,’) as [Calculated_PONum] from Erp.PODetail as PODetail where PODetail.PartNum = OrderDtl.PartNum FOR XML …

and here

BAQ: Calculated Field Concatenate Rows to single field

Is there a function/query that will allow multiple returned rows to be combined into a single field? I’m looking thru the PO Tables and finding all PO numbers for a specific job. The job may have 3 POs. PO Number : Job Number 70001 : 00245 70010 : 00245 70008 : 00245 I want to combine/concatenate these to be something like Job Number : PO Numbers 00245 : 70001,70010,70008

In my case, I want to connect the AprvMfg table and Manufacturer table such that each row of my result set consists of one column containing the part number and a second containing a list of all manufacturers for that parts. I even downloaded and imported the sample BAQ from @josecgomez and tweaked it for my needs. Yet my result still is like this (looking at three different parts for testing):

Where am I going wrong?

[Randy (Randy Stulce)]

If you have SQL 2017 or newer, I’d go with String_Agg it’s much easier. Here is a discussion on it. https://www.epiusers.hel/t/for-xml-path-versus-string-agg/71556

Here is a quick BAQ I setup in the psuedoSQL

select 
	[InvcHead].[Company] as [InvcHead_Company],
	[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
	(String_Agg(OrderNums.InvcDtl1_OrderNum,', ')) as [Calculated_SONum]
from Erp.InvcHead as InvcHead
left outer join  (select distinct
	[InvcDtl1].[Company] as [InvcDtl1_Company],
	[InvcDtl1].[InvoiceNum] as [InvcDtl1_InvoiceNum],
	[InvcDtl1].[OrderNum] as [InvcDtl1_OrderNum]
from Erp.InvcDtl as InvcDtl1)  as OrderNums on 
	InvcHead.Company = OrderNums.InvcDtl1_Company
	and InvcHead.InvoiceNum = OrderNums.InvcDtl1_InvoiceNum
group by [InvcHead].[Company],
	[InvcHead].[InvoiceNum]

wipString_Agg.baq (25.2 KB)

[Matthew_Morgan]

Thank you Randy, but unfortunately we are still on 2014.

[Randy (Randy Stulce)]

What version of E10 are you on? Can you upload your BAQ (minus any UD fields)?

[Matthew_Morgan]

We are on 10.2.500.15

BAQ:
test_QualMfgs2.baq (20.3 KB)

[Randy (Randy Stulce)]

Looks right per Jose’s BAQ which works. Have you tried swapping the tables so Manufacturer is the top table? We don’t use these tables so I have no data to test with.

select 
	[AprvMfg].[PartNum] as [AprvMfg_PartNum],
	(REPLACE(REPLACE(((select distinct
	(',' + Manufacturer.Name) as [Calculated_Name]
from Erp.Manufacturer as Manufacturer
where (Manufacturer.MfgNum = AprvMfg.MfgNum FOR XML PATH('')))) , '</Calculated_Name>',''),'<Calculated_Name>','')) as [Calculated_Names]
from Erp.AprvMfg as AprvMfg

[Banderson (Brandon Anderson)]

You need to join on part number if you want all the mfg’s to show on a part number. You are joining by mfg number.

[Matthew_Morgan]

There is no part number in the Manufacturer table, I’m not sure what you mean otherwise.

[Banderson (Brandon Anderson)]

Your subquery needs to look like this so that you can join on part number.

[bordway (Bruce Ordway)]

@Banderson

Your subquery needs to look like this so that you can join on part number

That is what I think too…
And attached a BAQ export of a quick example w/screenshot
(at least that is what I think you wanted)?

CUS-AprvMfg.baq (27.0 KB)

[Matthew_Morgan]

@bordway Yes, that’s exactly what we’re looking for, thanks! Brandon and Randy, thanks for your input as well.