Arrange output based on rows into columns

Shell Programming and Scripting
1

Hi All,

I would like to ask help on how can i achieve below output.

Inputfile:

 
Oct11,apa1-daily,01:25:01
Oct11,apa2-daily,01:45:23
Oct12,apa1-daily,02:30:11
Oct12,apa2-daily,01:55:01
Oct13,apa1-off,01:43:34
Oct13,apa2-off,01:22:04

Desired output:

 
Clients             Oct11      Oct12         Oct13
apa1-daily       01:25:01   02:30:11
apa1-off                                        01:43:34
apa2-daily       01:45:23   01:55:01
apa2-off                                        01:22:04

Thanks in advance
Mars

2

not quite what you're after, but this snippet should be close - you'll need to tweak the final formatting a little:

#  nawk -F, '
{
cl[$2]++
dl[$1]++
el[$2" "$1]=$3
}
END {
dd="Clients";for (d in dl){dd=dd","d}
print dd
for (c in cl){t=c;for (d in dl){t=t","el[c" "d]} print t}
}
' infile  
Clients,Oct11,Oct12,Oct13
apa1-daily,01:25:01,02:30:11,
apa1-off,,,01:43:34
apa2-daily,01:45:23,01:55:01,
apa2-off,,,01:22:04

HTH

1 Like
3 
awk -F, '
{a[$1 FS $2]=$3;b[$1]=$1;c[$2]=$2}
END { asort(b);asort(c);
      printf "Clients\t\t"; for (i in b) printf b"\t\t";printf "\n"
       for (i in c)
         {  printf c"\t";
            { for (j in b)
               printf (a[b[j] FS c]=="")?"\t\t":a[b[j] FS c]"\t"
            }
            printf "\n"
         }
    }
' infile |sort -n

Clients         Oct11           Oct12           Oct13
apa1-daily      01:25:01        02:30:11
apa1-off                                        01:43:34
apa2-daily      01:45:23        01:55:01
apa2-off                                        01:22:04

Not sure ,why asort(c) doesn't work as my expect, and I have to add |sort -n to sort it

1 Like
4

This is really what i need. Thank you so much. Both code works and just need for me to do some adjustment on format.

Really appreaciated your help.