Data Pivot

UNIX for Beginners Q & A
1

Good Day,

I have file input

6285296582710|20170509|INDOTEL
6285296835209|20170509|INDOTEL
6285296940311|20170509|INDOTEL
6285297027737|20170509|MULTIFLAG
6285297027737|20170509|DELTA
6285297304373|20170510|INDOTEL
6285297384129|20170510|INDOTEL
6285296940311|20170510|MULTIFLAG
6285297542691|20170516|AXES

I need to print $1 in $1 and $2 as header in horizontal then put $3

output

MSISDN|20170509|20170510|20170516
6285296582710|INDOTEL||
6285296835209|INDOTEL||
6285296940311|INDOTEL|MULTIFLAG|
6285297027737|MULTIFLAG,DELTA||
6285297304373||INDOTEL|
6285297384129||INDOTEL|
6285297542691|||AXES

Thanks

2

This is as unspecific a specification as can be. DON'T have people guessing what you want! Why should anybody care more for the problem than you do?
Where does the term "MSISDN" come from?

And, with quite some membership time and 139 posts, you should at least have some vague idea on how to tackle this problem. So - any attempts / ideas / thought from your side?

1 Like
3

It is a pity there was no follow-up, you really learn a lot more if you post your attempts..

Anyway, here is an example of what a solution might look like:

awk '
  NR==FNR {
    A[$1,$2]=A[$1,$2] (A[$1,$2]?",":"") $3
    B[$1]
    if (!($2 in H)) {
      h=h OFS $2
      H[$2]=c++
    } 
    next
  }

  FNR==1 {
    print "MSISDN" h
  } 

  $1 in B {
    for(i in H)
      $(H+2)=A[$1,i]
    print
    delete B[$1]
  }
' FS=\| OFS=\| file file

MSISDN|20170509|20170510|20170516
6285296582710|INDOTEL||
6285296835209|INDOTEL||
6285296940311|INDOTEL|MULTIFLAG|
6285297027737|MULTIFLAG,DELTA||
6285297304373||INDOTEL|
6285297384129||INDOTEL|
6285297542691|||AXES

--
Note: the file name needs to be specified twice so that it gets read twice..