CONCAT function with Sort weight

[manideep78]

Dear Community,

I have an input like table 1 and expecting output like table2.

I would like to use Concat function without distinct but my priority is sort order. My sort order is based on Col1 and Col2 and ignore the default concat sort.

either script function or chart function is fine, but recommend in script because I have to display my output in table box.

Table-1: Input

Col1	Col2	Col3	Col4	Col5
1	A	AA	3	PQR
1	B	BB	1	ABC
1	C	CC	2	XYZ
2	X	XX	100	XXX
2	Y	YY	101	YYY
3	P	PP	200	PPP
3	P	PP	350	PPP
3	Q	QQ	250	QQQ
3	R	RR	300	RRR

Table -2: Output

Col1	Col2	Col3	Col4	Col5
1	A,B,C	AA,BB,CC	3,1,2	PQR,ABC,XYZ
2	X,Y	XX,YY	100,101	XXX,YYY
3	P,P,Q,R	PP,PP,QQ,RR	200,350,250,300	PPP,PPP,QQQ,RRR

Thanks in advance!

[Anil_Babu_Samineni]

PFA, IF this is what you expecting?

[Taoufiq_Zarra]

@manideep78  May be this version :

Input:

LOAD * INLINE [
    Col1, Col2, Col3, Col4, Col5
    1, A, AA, 3, PQR
    1, B, BB, 1, ABC
    1, C, CC, 2, XYZ
    2, X, XX, 100, XXX
    2, Y, YY, 101, YYY
    3, P, PP, 200, PPP
    3, P, PP, 350, PPP
    3, Q, QQ, 250, QQQ
    3, R, RR, 300, RRR
];

output:
noconcatenate


load Col1,concat(Col2,',',recno()) as Col2,concat(Col3,',',recno()) as Col3,concat(Col4,',',recno()) as Col4,concat(Col5,',',recno()) as Col5 resident Input group by Col1;

drop table Input;

output :

[Taoufiq_Zarra · Accepted solution]

@manideep78  or this :

Input:

LOAD *,rowno() as Numtmp INLINE [
    Col1, Col2, Col3, Col4, Col5
    1, A, AA, 3, PQR
    1, B, BB, 1, ABC
    1, C, CC, 2, XYZ
    2, X, XX, 100, XXX
    2, Y, YY, 101, YYY
    3, P, PP, 200, PPP
    3, P, PP, 350, PPP
    3, Q, QQ, 250, QQQ
    3, R, RR, 300, RRR
];

output:
noconcatenate


load Col1,concat(Col2,',',Numtmp) as Col2,concat(Col3,',',Numtmp) as Col3,concat(Col4,',',Numtmp) as Col4,concat(Col5,',',Numtmp) as Col5 resident Input group by Col1;

drop table Input;

output:

[manideep78]

This has the sort issue in col4, row 3.

it should be

200,350,250,300

[manideep78]

I had to tweak little bit because I cannot drop the table as I have more than 100 columns and cannot mention all of them in group by.

I just took the key and the columns that need to be concatenated in a separate table using resident load and then applied the similar logic what you said. so, I can accept this as correct solution.