

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
CONCAT function with Sort weight
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 | 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!
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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:
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
PFA, IF this is what you expecting?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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:
Taoufiq ZARRA
"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
(you can mark up to 3 "solutions") 😉


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This has the sort issue in col4, row 3.
it should be
200,350,250,300 |


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
