I am facing a problem in creating a logic and would require your help for that.
I have a table which looks something like
I want a result which should look like
As you can see, column C3 has 5 distinct values 1,2,3,4 and 5 . The count for max values for C3 is 3 hence 3 new columns are added in the output table (T1,T2 and T3).
If supposingly we had 4 as the max count then 4 new columns should be added.
The values in T1, T2 and T3 are populated on the basis of C2.
Please help guys its urgent.
Thanking you all in anticipation
1. Use Concat for C2:
Concat(C2,',',C2) as Temp
Group by C3;
2. Use Subfield to create the new Fields:
for i=1 to vMaxCnt
SubField(Temp,',',$(i)) as 'T$(i)'
DROP Table TAB1;
RENAME Table TAB2 to TAB1;
View solution in original post
a better solution for part 2 is to build a "Script line" in the loop:
if i=1 then
set vScript="subfield(Temp,',',$(i)) as T$(i)";
set vScript="$(vScript),subfield(Temp,',',$(i)) as T$(i)";