UNIX append field with comparing fields from multiple column

Shell Programming and Scripting
1

I have a csv dump from sql server that needs to be converted so it can be feed to another program. I already sorted on field 1 but there are multiple columns with same field 1 where it needs to be compared against and if it is same then append field 5.

i.e from

ANG SJ,0,B,LC22,LC22(0)
BAT SJ,0,B,LC22,LC22(0)
BAT SJ,0,B,LC22,LC22(0)
BGA SJ,40000,B,SL22,SL22(40000)
BGA SJ,40000,B,SL22,SL22(40000)
BGA SJ,50000,B,SL22,SL22(50000)
BHW PW,152000,B,EX22,EX22(152000)
BKIR ID,15000000,B,GSI,GSI(15000000)
BKIR ID,15000000,B,GSI,GSI(15700000)
BKIR ID,15000000,B,GSI,GSI(15000000)
BKIR ID,15900000,B,GSI,GSI(15900000)
EBS AV,0,B,LC22,LC22(0)
EBS AV,1,B,LC22,LC22(1)
EBS AV,2,B,LC22,LC22(2)
EBS AV,3,B,LC22,LC22(3)
EBS AV,4,B,LC22,LC22(4)

it needs to be converted to following:

ANG SJ,0,B,LC22,LC22(0)
BAT SJ,0,B,LC22(0)|LC22(0)
BGA SJ,40000,B,SL22(40000)|SL22(40000)|SL22(50000)
BHW PW,152000,B,EX22,EX22(152000)
BKIR ID,15000000,B,GSI(15000000)|GSI(15700000)|(15000000)|(15900000)
EBS AV,0,B,LC22(0)|LC22(1)|LC22(2)|LC22(3)|LC22(4)

.

problem is sometime there is single filed 1 or it could be two or three or four times.

2

Try something like

awk '{A[$1]=!C[$1]++? $0 : A[$1] OFS $NF} END{for(i in A) print A}' FS=, OFS=\| file
1 Like
3

Thanks a lot.