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.