Joining columns

[philips217]

Hi guys,
I am new to AWK and am trying to create a script to join rows together based on a certain column value in common.

Ex:
p_id,cost,place,p_id,name,last_name,
,,,,,,
1,23,h,0,bob,bob,
1,14,g,1,frank,frank,
1,22,j,2,grant,grant,
1,12,k,3,dan,dan,
2,12,q,4,jack,jack,
3,11,o,,,,
,,,,,

Will output:
p_id,cost,place,p_id,name,last_name,
,,,,,,
1,23,h,frank,frank,
1,14,g,frank,frank,
1,22,j,frank,frank,
1,12,k,frank,frank,
2,12,q,grant,grant
3,11,o,dan,dan,

So for every row, I print out column 1,2,3 then try and match its value with its corresponding value in column 4. When I find the value I print out column 5 and 6 that match up.

This is what I have tried:
BEGIN {
FS=",";
}
{
for ( i in $2 ) {
for ( j in $9 ) {
if ( i == j ) {
print $1,$2,$3,$4,$5,$6,$7,$8,$9,;
}
}
}
}

Sorry that I am really bad at this, I am just trying to learn. Its not letting me use the for(i in $2). Do I have to first go through and store everything in an array?

Any help would be greatly appreciated.

Thanks.

 

[will27]

suppose your data file is named "myfile" and an unique number at field 4 correspond to a unique name, do this

awk 'BEGIN{FS=OFS=","} NR==FNR {namecode[$4]=$5;next} {print $1,$2,$3,namecode[$1],namecode[$1]}' myfile myfile

tested and works!
will27