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

I am attempting to write a sql statement that displays a list of operations per job and assembly sequence. Right now it lists all of the operations for all of the assemblies.

The SQL so far:

select distinct
jobhead.JobNum, JobHead.PartNum,jobhead.ProdCode as ProdGroup,joboper.AssemblySeq as ASM,
(REPLACE(REPLACE(((select
(case when jobhead.JobClosed=1 and joboper.OpComplete=1 then CAST(concat(joboper.AssemblySeq,’:’,joboper.oprseq,’:’,joboper.opcode) AS VARCHAR) + ‘,’ else ‘’ end) as OpCode
from Erp.JobOper as JobOper where JobOper.JobNum = jobhead.jobNum order by case when joboper.AssemblySeq=0 then 999 else joboper.AssemblySeq end
FOR XML PATH(’’))) , ‘’,’’),’’,’’)) as Operations
from Erp.JobHead as Jobhead inner join erp.joboper as joboper on jobhead.jobnum=joboper.JobNum

Here is an example of the current results:

JobNum Part ProdGroup ASM Operations
1234 ABC Group1 0 1:10:XX05,1:20:XX05,1:30:ZZ01,1:40:CC01,1:50:QC01,2:10:GF01,2:20:XX05,2:30:XX05,2:40:ZZ01,2:45:ZZ01,2:50:CC01,2:60:QC01,0:10:ASY01,0:20:QC01,
1234 ABC Group1 1 1:10:XX05,1:20:XX05,1:30:ZZ01,1:40:CC01,1:50:QC01,2:10:GF01,2:20:XX05,2:30:XX05,2:40:ZZ01,2:45:ZZ01,2:50:CC01,2:60:QC01,0:10:ASY01,0:20:QC01,
1234 ABC Group1 2 1:10:XX05,1:20:XX05,1:30:ZZ01,1:40:CC01,1:50:QC01,2:10:GF01,2:20:XX05,2:30:XX05,2:40:ZZ01,2:45:ZZ01,2:50:CC01,2:60:QC01,0:10:ASY01,0:20:QC01,

How I want this example to look:

JobNum Part ProdGroup ASM Operations
1234 ABC Group1 0 0:10:ASY01,0:20:QC01,
1234 ABC Group1 1 1:10:XX05,1:20:XX05,1:30:ZZ01,1:40:CC01,1:50:QC01
1234 ABC Group1 2 2:10:GF01,2:20:XX05,2:30:XX05,2:40:ZZ01,2:45:ZZ01,2:50:CC01,2:60:QC01

check out this example, it will do exactly what you are looking for in a BAQ. If you import the BAQ you should be able to look at the SQL, (that’s where I stop being helpful, as I don’t know SQL and I just use BAQ’s for pretty much everything)

1 Like

Here is a SQL statement that I think does what you are looking for:
select distinct
jobhead.JobNum, JobHead.PartNum,jobhead.ProdCode as ProdGroup,joboper.AssemblySeq as ASM,
substring((select ‘,’ + concat(JobOper2.AssemblySeq,’:’,JobOper2.OprSeq,’:’,JobOper2.OpCode)
from Erp.JobOper JobOper2
where JobOper.Company=JobOper2.Company and JobOper.JobNum=JobOper2.JobNum and JobOper.AssemblySeq=JobOper2.AssemblySeq
and JobHead.JobClosed=1 and JobOper2.OpComplete=1
order by JobOper2.Company, JobOper2.JobNum, JobOper2.AssemblySeq, JobOper2.OprSeq
for xml path(’’)),2,9999) as Operations
from Erp.JobHead as Jobhead
inner join erp.joboper as joboper on JobHead.Company=JobOper.Company and jobhead.jobnum=joboper.JobNum
where JobHead.JobClosed=1 and JobOper.OpComplete=1

1 Like

This is exactly what I wanted. Thank you!