I am trying to concatenate multiple rows to a single field in a query and am wondering how to do it in a BAQ.
Below are a couple of different ways I can do it in a SQL query.
Select distinct ST2.JobNum,
Select ‘,’+ ST1.OpCode AS [text()]
From Erp.JobOper ST1
Where ST1.JobNum = ST2.JobNum
ORDER BY ST1.JobNum
For XML PATH (’’)
), 2, 1000) [OpCode]
From Erp.JobOper ST2
WHERE ST2.JobNum = ‘007373’
DECLARE @Operations VARCHAR(8000)
SELECT @Operations = COALESCE(@Operations + ', ', ‘’) + Erp.JobOper.OpCode
WHERE Erp.JobOper.OpCode IS NOT NULL AND Erp.JobOper.JobNum = ‘007373’ AND Erp.JobOper.OpComplete = 0
SELECT OpCode + ‘, ’ AS ‘data()’
WHERE Erp.JobOper.JobNum = ‘007373’
FOR XML PATH(’’)
All of these give me what I need in the SQL management studio.
Has anybody made this work in a BAQ or have other advice on how to deploy this? My next thought is to create the entire thing in a stored procedure and use an external BAQ but am trying to prevent that if possible.
One of our IT guys created a function under the Aggregate functions in SQL DB called dbo.Agregate. When grouping, we call this function in our BAQ calculated field : dbo.Agregate(field to concatenate) same as if we would do a sum(field). The result is the calculated field having all the grouped fields values.
Sorry , I think it is normal…
That’s what CreateTo new query gave me. I was not the one who generated this and I am not that advanced into scripting with SQL …
like What EXTERNAL NAME does ? I think it is calling so external dll I am not aware of…