I have read other Topics concerning concatenating unfortunately I am not super Tech Savvy and I really don’t understand what I have read.
What I want to do may be above my comprehension and if you feel like it is - just say so.
I can do what I want to do in Excel however I would like to do it in my BAQ rather than teaching others what steps they have to take to achieve the concatenation in Excel (one stop shopping!)
So below is a Method for part 123. Instead of listing the operations vertically I would rather lump them all together on one line (see: “What I would like to achieve” below)
Typical Method - Part No 123
10 Shear
20 Holex
30 Form
What I would like to achieve:
Col1 Col2
*123 Shear, Holex, Form
Typical Method - Part No 456
10 Shear
20 Holex
30 Holex
40 Form
50 TimeSave
What I would like to achieve:
Col1 Col2 456 Shear, Holex, Holex, Form, TimeSave
STRING_AGG was added in…I believe…SQL Server 2016. Are you running that or higher?
I know with our system, we uplifted an earlier database. I had to manually change the compatibility level to at least 130 (representing Server 2016) enable String_Agg and other features even though we were running a much newer SQL edition. The Perf & Diagnostic Tool will tell you what level you’re on.
I tried making the changes to my BAQ that were shown in the example BAQ. This time it ran but I had to stop it because nothing happened, but I received some untrustworthy results
Anyway…here is the code - please note that I use the GUI interface to create the BAQ’s and add in the SQL when I can figure it out!
select
[Part].[PartNum] as [Part_PartNum],
(REPLACE(REPLACE(((select
[PartRev].[RevisionNum] as [PartRev_RevisionNum],
(CAST(PartOpDtl.ResourceGrpID AS VARCHAR) + ',') as [Calculated_ResourceID]
from Erp.Part as Part1
inner join Erp.PartOpDtl as PartOpDtl on
Part1.Company = PartOpDtl.Company
and Part1.PartNum = PartOpDtl.PartNum
and ( not PartOpDtl.ResourceGrpID like '%WF-' )
inner join Erp.PartRev as PartRev on
PartOpDtl.Company = PartRev.Company
and PartOpDtl.PartNum = PartRev.PartNum
and PartOpDtl.RevisionNum = PartRev.RevisionNum
and PartOpDtl.AltMethod = PartRev.AltMethod
and ( PartRev.Approved = TRUE )
where PartOpDtl.ResourceGrpID = PartOpDtl.ResourceGrpID FOR XML PATH(''))) , '</Calculated_ResourceID',''),'<Calculated_ResourceID>','')) as [Calculated_OPs],
[Part].[ProdCode] as [Part_ProdCode],
[Part].[GrossWeight] as [Part_GrossWeight],
[Part].[NonStock] as [Part_NonStock],
[Part].[Number02] as [Part_Number02],
[Part].[Number03] as [Part_Number03],
[Part].[Number04] as [Part_Number04],
[Part].[Number05] as [Part_Number05],
[Part].[Character01] as [Part_Character01],
[Part].[Character02] as [Part_Character02],
[Part].[Character03] as [Part_Character03],
[Part].[NationalMatStds_c] as [Part_NationalMatStds_c],
[Part].[CustomerMatStds_c] as [Part_CustomerMatStds_c],
[Part].[TypeCode] as [Part_TypeCode]
from Erp.Part as Part
where (Part.TypeCode = 'M' and Part.ProdCode like @ProdCode)
That error (where stuff doesn’t show up until you hover over it) happens when you have too much stuff in a single cell. You’re going to have to find a way to reduce how much is going into each cell.
If I strip out your custom stuff, this is what’s showing in that field. So it comes back, but you still have all of the XML stuff in there. You’ll need to do the string manipulation to get rid of all of that.
Keep in mind, the function FOR XML PATH() is used to take a query and make it XML for some other program to read (like a webpage or something). It shoves it all into one cell and adds the XML tags in it.
This really wasn’t intended to be used a concatenate function, someone just noticed that you could, with some string manipulation to get rid of the XML tags.
