Help: Create 'FieldName'&1, 'FieldName'&2... ect from one 'FieldName' in one table.

[cbailey1201]

Hello,

I have a table that contains a KEY (in my case [Part]), the [Part] is listed as many times as there are entries.

Example:

XRefCount	Part	VendorID	VendorName	VendorPart
1	111-1122-22	26	Vendor 1	Part-1
1	222-2211-11	103	Vendor 2	Part-2
2	222-2211-11	150	Vendor 3	Part-3
3	222-2211-11	250	Vendor 4	Part-4
1	122-3333-33	300	Vendor 5	Part-5
2	122-3333-33	350	Vendor 6	Part-6
3	122-3333-33	500	Vendor 7	Part-7

I want to have 1 table with Distinct [Part] and have a field for each value, VendorID1, VendorID2, VendorID3...ect.

End Result:

Part	VendorID1	VendorName1	VendorPart1	VendorID2	VendorName2	VendorPart2	VendorID3	VendorName3	VendorPart3
111-1122-22	26	Vendor 1	Part-1
222-2211-11	103	Vendor 2	Part-2	150	Vendor 3	Part-3	250	Vendor 4	Part-4
122-3333-33	300	Vendor 5	Part-5	350	Vendor 6	Part-6	500	Vendor 7	Part-7

My data table has up to 8 [VendorID] values per [Part]. as well as 7 Fields (besides the [Part] and [XrefCount]) that I need to have numbered fields up to 8.

There are 43k rows in this table, and 40k distinct [Part]'s.

Thank you in Advance!

[vinieme12 · Accepted solution]

@cbailey1201 

the error is below, the variable is not being cleared before generation of the fieldlist

Add the below line just  before For loop

let vColList='';

for i = 1 to $(vMax);

vColList = '$(vColList)' & if($(i)=1,'',',') &'MaxString(if(XRefCount=$(i),VendorID)) as VendorID$(i)
, MaxString(if(XRefCount=$(i),VendorName)) as VendorName$(i)
, MaxString(if(XRefCount=$(i),VendorPart)) as VendorPart$(i)' ;

next i;

, MaxString(if(XRefCount=8,VendorPart)) as VendorPart8MaxString>>>>>>(<<<<<<if(XRefCount=1,VendorID)) as VendorID1

, MaxString(if(XRefCount=1,VendorName)) as VendorName1

[cbailey1201]

@vinieme12  Awesome thank you!!

I didn't know you need to clear the variable before the loop in the load script. But that makes sense now.