Shell Programming and Scripting

ID|Description|Column1|Column2|Column3|Column4|
1|ABC DEF GHJ JJJ|ABC|DEF|GHJ|JJJ|
2|AHJ VGH SDHKJHK GKK|AHJ|VGH|SDHKJHK|GKK,AHJ,VGH|
3|123 HGJ KLL|123|HGJ|KLL||
4|345 BNN NMNMN SHDJ|345|BNN|NMNMN|SHDJ,345 BNN|

$ awk -F'|' '{ for(i=1;i<=NF;i++){  for(j in A){ if($i ~ j){ print; delete A; next} }  p=$i; gsub(/[[:space:]]+/,"|",p); A[p] } delete A  }' file

1|Column 1|ABC|
1|Column 4|ABC|
4|Column 1|345|
4|Column 4|345|
4|Column 1|BNN|
4|Column 4|BNN|

ID|Column|Value|
2|Column 1|AHJ|
2|Column 4|AHJ|
4|Column 1|345|
4|Column 4|345|
4|Column 2|BNN|
4|Column 4|BNN|

awk 'BEGIN{FS = OFS = "|"}
  NR == 1 {split($0, h);
    print "ID", "Column", "Value";
    next}
  {split($2, a, " |,");
  for(x in a)
    {f = 0; for(i=3; i<=NF; i++)
        {if($i ~ "(^|,| )" a[x] "(,| |$)" && f)
          {print $1, h[j], a[x];
          print $1, h[i], a[x];
          f = 0;
          continue}
        if($i ~ "(^|,| )" a[x] "(,| |$)")
          {f = 1; j = i}}}}' file

ID|Column|Value
2|Column1|AHJ
2|Column4|AHJ
2|Column2|VGH
2|Column4|VGH
4|Column1|345
4|Column4|345
4|Column2|BNN
4|Column4|BNN