Shell Programming and Scripting

sno    nm    no    nm2    ID
1    cc    574372    yyyi    |6810|51234|
2    bb    119721    nmjk    |6810|51234|51179|    
3    ab    54161    mkkjn    |48193|46907|

ID        Description
6810    verygood_output
51234    Awesome work
46907    notbad
51179    excellent effort
48193    can do better

sno    nm    no    nm2    Description    
1    cc    574372    yyyi    verygood_output;Awesome work
2    bb    119721    nmjk    verygood_output;Awesome work;excellent effort
3    ab    54161    mkkjn    notbad;can do better

awk 'NR==FNR{for (i=2;i<=NF;i++) a[$1]=a[$1](i==2?"":" ")$i}NR!=FNR{for (i in a) gsub(i,a[i],$5);gsub("\\|",";",$5);gsub("^;|;$","",$5);print}' file2 file1

awk 'NR==FNR{match($0,"([^ ]+) +(.+)",x);_[x[1]]=x[2];next}FNR==1{$NF=_[$NF]}
FNR>1{n=split($NF,x,"|");$NF="";for(i=1;++i<n;){z=_[x[i]];$NF=$NF?$NF";"z:z}}1' file2 file1

awk 'NR==FNR {
  id=$1
  sub("^[[:alnum:]]+[[:space:]]+","")
  a[id]=$0
  next
}
{
  n=split($NF,b,"|")
  sub("[[:alnum:]|]+ *$","")
  printf "%s",$0
  sep=""
  for (i=1; i<=n; ++i) if (b[i] in a) {printf "%s",sep a[b[i]]; sep=";"}
  print ""
}' file2 file1

awk '
  NR==FNR{
    A[$1]=$2
    next
  } 
  FNR==1{
    sub("ID",A["ID"])
  }
  {
    for(i=2; i<NF; i++) $i=A[$i]
    sub(/;/,x)
    sub(/; *$/,x)
  }
  1
' FS='   +' file2 FS=\| OFS=\; file1