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 :…

You can use one of these methods to do it I’ve done it with the XML method in E10 [image] Concatenating Row Values in Transact-SQL - Simple Talk It is an interesting problem in Transact SQL, for which there are a number of solutions and considerable debate. How do you go about producing …

Thanks. Looking at the XML method, it shows examples of the SQL code, but I don’t have a way to manually change the SQL “Query Phrase”, I have to use the GUI “Query Builder.” Do I set the query type as a CTE type or keep as TopLevel / InnerSubQuery? How to I get the SQL code " FOR XML PATH(’’), TY…

You can write that exact thing with the query builder using subqueries

Here is an example @WesAtITC CustOrder.baq (17.6 KB) [image]

Awesome Jose this is quite a useful query. I have one question. What if you are trying to join something that has a multiple index? For example to concatenate the reference designators on a Job material, for each material one would need to link JobNum,AssemblySeq and MtlSeq.

I did something similar for Job Operations on the Subquery, using OprSeq and OpCode: CAST(JobOper.OprSeq as varchar) + ’ ’ + CAST(JobOper.OpCode as varchar) + ’ ’ And then concatenate the field in the same way on the top level query. Yours should just take one extra level from there.

This fixed my issue. Mark, You can just keep adding rows as well in the sub query. The last row has the XML at the end.

Just to make this code even better. I was able to remove the last comma (,) on the string with the following. REVERSE(stuff(REVERSE(REPLACE(REPLACE({JobnECR} , ‘</Calculated_Calculated_ECR>’,’’),’<Calculated_Calculated_ECR>’,’’)), 1, 1, ‘’)) vs REPLACE(REPLACE({JobnECR} , ‘</Calculated_Calculate…

Ken, thanks for the help. I was able to narrow it down so that only reference designators related to the material line are brought in. Getting rid of the superfluous comma also worked great. I learned that pasting straight into BAQ Designer requires replacing all of your single quotes or an error i…

I called my calculated field Calculated_ECR, so it was turned into Calculated_Calculated_ECR lol. Glad it worked out for ya.

BAQ: Calculated Field Concatenate Rows to single field

Experts' Corner

josecgomez (Jose C Gomez) June 2, 2017, 7:43pm 5

Here is an example @WesAtITC
CustOrder.baq (17.6 KB)

Multiple lines in BAQ to consolidate into 1 line item

BAQ incorrect syntax near 'FOR'

SQL help! Comma separated operations list by assembly using FOR XML PATH

BAQ with Subquery

Top 5 Products sold

Topic		Replies	Views
BAQ Concatenate Rows Columns to Field Epicor ERP 10	14	10641	April 30, 2018
Concatenate multiple rows into one row, with criteria Epicor ERP 10	5	3336	January 8, 2019
BAQ - Concatenate results from subquery into one field Epicor ERP 10 baq	7	2968	August 30, 2023
What's going on here? Epicor ERP 10 e10 , baq	10	851	October 26, 2021
Concatenate OP's in order of OP Sequence Epicor ERP 10 baq	9	881	January 30, 2024

BAQ: Calculated Field Concatenate Rows to single field

Related topics