Hi,

I have the following output:
 Number of executions               = 437
 Number of compilations             = 1
 Worst preparation time (ms)        = 1
 Best preparation time (ms)         = 1
 Rows deleted                       = 0

 Number of executions               = 1
 Number of compilations             = 1
 Worst preparation time (ms)        = 4
 Best preparation time (ms)         = 4
 Rows deleted                       = 0


 Number of executions               = 29
 Number of compilations             = 1
 Worst preparation time (ms)        = 1
 Best preparation time (ms)         = 1
 Rows deleted                       = 0
 
..........

How can I tranpose certain rows into columns and sort by one of the
column? Like the following which is sorted by :Number of executions":
Number of executions   Number of compilations     Rows deleted
437                    1                          0
29                     1                          0
29                     1                          0
....

Thanks
Weidong

Weidong wrote:
> Hi,
> 
> I have the following output:
>  Number of executions               = 437
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 1
>  Best preparation time (ms)         = 1
>  Rows deleted                       = 0
> 
>  Number of executions               = 1
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 4
>  Best preparation time (ms)         = 4
>  Rows deleted                       = 0
> 
> 
>  Number of executions               = 29
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 1
>  Best preparation time (ms)         = 1
>  Rows deleted                       = 0
>  
> .........
> 
> How can I tranpose certain rows into columns and sort by one of the
> column? Like the following which is sorted by :Number of executions":
> Number of executions   Number of compilations     Rows deleted
> 437                    1                          0
> 29                     1                          0
> 29                     1                          0

How about something like,

awk '
/executions/{ E = $NF }
/compilations/ { C = $NF }
/deleted/ { A[++i] = sprintf("%10s%10s%10s", E, C, $NF)  }
END { for (; i > 0; i--) print A[i] | \
    "sort -r -k" COL "," COL }' COL=1 file


-- 
Regards,

---Robert

Robert Katz wrote:
> Weidong wrote:
> 
>> Hi,
>>
>> I have the following output:
>>  Number of executions               = 437
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 1
>>  Best preparation time (ms)         = 1
>>  Rows deleted                       = 0
>>
>>  Number of executions               = 1
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 4
>>  Best preparation time (ms)         = 4
>>  Rows deleted                       = 0
>>
>>
>>  Number of executions               = 29
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 1
>>  Best preparation time (ms)         = 1
>>  Rows deleted                       = 0
>>  
>> .........
>>
>> How can I tranpose certain rows into columns and sort by one of the
>> column? Like the following which is sorted by :Number of executions":
>> Number of executions   Number of compilations     Rows deleted
>> 437                    1                          0
>> 29                     1                          0
>> 29                     1                          0
> 
> 
> How about something like,
> 
> awk '
> /executions/{ E = $NF }
> /compilations/ { C = $NF }
> /deleted/ { A[++i] = sprintf("%10s%10s%10s", E, C, $NF)  }
> END { for (; i > 0; i--) print A[i] | \
>    "sort -r -k" COL "," COL }' COL=1 file

Why save it in an array instead of...

/executions/{ E = $NF }
/compilations/ { C = $NF }
/deleted/ { printf("%10s%10s%10s\n", E, C, $NF) | "sort -nr" }

Wouldn't any buffering problem also accur in the END clause?

Janis

Weidong wrote:
> Hi,
> 
> I have the following output:
>  Number of executions               = 437
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 1
>  Best preparation time (ms)         = 1
>  Rows deleted                       = 0
> 
>  Number of executions               = 1
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 4
>  Best preparation time (ms)         = 4
>  Rows deleted                       = 0
> 
> 
>  Number of executions               = 29
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 1
>  Best preparation time (ms)         = 1
>  Rows deleted                       = 0
>  
> .........
> 
> How can I tranpose certain rows into columns and sort by one of the
> column? Like the following which is sorted by :Number of executions":
> Number of executions   Number of compilations     Rows deleted
> 437                    1                          0
> 29                     1                          0
> 29                     1                          0
> ...
> 
> Thanks
> Weidong

If you really have a blank line between each record, then this will do 
it all in gawk, including printing the headings and sorting the output:

gawk -vRS="" -F"\n" 'BEGIN{ fields = "1 3 5"; key = "3" }
{
         for (i=1; i<=NF;i++) {
                 split($i,f,"=")
                 sub(/[[:blank:]]*$/,"",f[1])
                 title[i]=f[1]
                 value[i]=f[2]+0
                 fmt[i]="%-"(length(title[i])+3)"s"
         }
         numflds = split(fields,flds," ")
         lines[NR] = sprintf("%020s ",value[key])
         for (i=1; i<=numflds; i++) {
                 lines[NR] = lines[NR] sprintf(fmt[flds[i]], value[flds[i]])
         }
}
END {
         for (i=1; i<=numflds; i++) {
                 printf fmt[flds[i]], title[flds[i]]
         }
         print ""
         asort(lines)
         for (i=1; i<=NR; i++) {
                 sub("[[:digit:]]* ","",lines[i])
                 print lines[i]
         }
}'

Setting fields and key at the beginning obvious dictates which fields to 
be printed and which key to sort on. The only thing it assumes about 
field sizes is that your key fields values won't be more than 20 
characters. Yes, I did spend way too much time thinking about this one - 
just seemed like I'd have to use it myself some day....

	Ed.

Janis Papanagnou wrote:
> Robert Katz wrote:
> 
>> Weidong wrote:
>>
>>> Hi,
>>>
>>> I have the following output:
>>>  Number of executions               = 437
>>>  Number of compilations             = 1
>>>  Worst preparation time (ms)        = 1
>>>  Best preparation time (ms)         = 1
>>>  Rows deleted                       = 0
>>>
>>>  Number of executions               = 1
>>>  Number of compilations             = 1
>>>  Worst preparation time (ms)        = 4
>>>  Best preparation time (ms)         = 4
>>>  Rows deleted                       = 0
>>>
>>>
>>>  Number of executions               = 29
>>>  Number of compilations             = 1
>>>  Worst preparation time (ms)        = 1
>>>  Best preparation time (ms)         = 1
>>>  Rows deleted                       = 0
>>>  
>>> .........
>>>
>>> How can I tranpose certain rows into columns and sort by one of the
>>> column? Like the following which is sorted by :Number of executions":
>>> Number of executions   Number of compilations     Rows deleted
>>> 437                    1                          0
>>> 29                     1                          0
>>> 29                     1                          0
>>
>>
>>
>> How about something like,
>>
>> awk '
>> /executions/{ E = $NF }
>> /compilations/ { C = $NF }
>> /deleted/ { A[++i] = sprintf("%10s%10s%10s", E, C, $NF)  }
>> END { for (; i > 0; i--) print A[i] | \
>>    "sort -r -k" COL "," COL }' COL=1 file
> 
> 
> Why save it in an array instead of...
> 
> /executions/{ E = $NF }
> /compilations/ { C = $NF }
> /deleted/ { printf("%10s%10s%10s\n", E, C, $NF) | "sort -nr" }

I didn't realize that would work.

> 
> Wouldn't any buffering problem also accur in the END clause?

I don't know.

Those were serious answers.

-- 
Regards,

---Robert

In comp.lang.awk Weidong <wdding@hotmail.com>:
> Hi,

> I have the following output:
> Number of executions               = 437
> Number of compilations             = 1
> Worst preparation time (ms)        = 1
> Best preparation time (ms)         = 1
> Rows deleted                       = 0

> Number of executions               = 1
> Number of compilations             = 1
> Worst preparation time (ms)        = 4
> Best preparation time (ms)         = 4
> Rows deleted                       = 0


> Number of executions               = 29
> Number of compilations             = 1
> Worst preparation time (ms)        = 1
> Best preparation time (ms)         = 1
> Rows deleted                       = 0
> 
> .........

> How can I tranpose certain rows into columns and sort by one of the
> column? Like the following which is sorted by :Number of executions":
> Number of executions   Number of compilations     Rows deleted
> 437                    1                          0
> 29                     1                          0
> 29                     1                          0
> ...

awk '/executions/||/compilations/||/deleted/{
printf "%10s",$NF}NF==0{print ""}END{print ""}' infile

Should be on one line, left piping through 'sort' and adding the
header as exercise for you.

-- 
Michael Heiming (X-PGP-Sig > GPG-Key ID: EDD27B94)
mail: echo zvpunry@urvzvat.qr | perl -pe 'y/a-z/n-za-m/'
#bofh excuse 446: Mailer-daemon is busy burning your message
in hell.

Ed Morton wrote:
> 
> 
> Weidong wrote:
> 
>> Hi,
>>
>> I have the following output:
>>  Number of executions               = 437
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 1
>>  Best preparation time (ms)         = 1
>>  Rows deleted                       = 0
>>
>>  Number of executions               = 1
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 4
>>  Best preparation time (ms)         = 4
>>  Rows deleted                       = 0
>>
>>
>>  Number of executions               = 29
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 1
>>  Best preparation time (ms)         = 1
>>  Rows deleted                       = 0
>>  
>> .........
>>
>> How can I tranpose certain rows into columns and sort by one of the
>> column? Like the following which is sorted by :Number of executions":
>> Number of executions   Number of compilations     Rows deleted
>> 437                    1                          0
>> 29                     1                          0
>> 29                     1                          0
>> ...
>>
>> Thanks
>> Weidong
> 
> 
> If you really have a blank line between each record, then this will do 
> it all in gawk, including printing the headings and sorting the output:
> 
> gawk -vRS="" -F"\n" 'BEGIN{ fields = "1 3 5"; key = "3" }
> {
>         for (i=1; i<=NF;i++) {
>                 split($i,f,"=")
>                 sub(/[[:blank:]]*$/,"",f[1])
>                 title[i]=f[1]
>                 value[i]=f[2]+0
>                 fmt[i]="%-"(length(title[i])+3)"s"
>         }
>         numflds = split(fields,flds," ")
>         lines[NR] = sprintf("%020s ",value[key])
>         for (i=1; i<=numflds; i++) {
>                 lines[NR] = lines[NR] sprintf(fmt[flds[i]], value[flds[i]])
>         }
> }
> END {
>         for (i=1; i<=numflds; i++) {
>                 printf fmt[flds[i]], title[flds[i]]
>         }
>         print ""
>         asort(lines)
>         for (i=1; i<=NR; i++) {
>                 sub("[[:digit:]]* ","",lines[i])
>                 print lines[i]
>         }
> }'
> 
> Setting fields and key at the beginning obvious dictates which fields to 
> be printed and which key to sort on. The only thing it assumes about 
> field sizes is that your key fields values won't be more than 20 
> characters. Yes, I did spend way too much time thinking about this one - 
> just seemed like I'd have to use it myself some day....
> 
>     Ed.

It occurs to me the above isn't exactly obvious in what it's doing or 
why, so here's a commented version (with one tweak - moving setting of 
"flds" to the BEGIN section):

gawk -vRS="" -F"\n" 'BEGIN{ fields = "1 2 5"; key = "1"
     numflds = split(fields,flds," ")
}
{
     for (i=1; i<=NF;i++) {
         split($i,f,"=")
         # Get rid of all spaces from the end of the title text
         sub(/[[:blank:]]*$/,"",f[1])
         title[i]=f[1]
         # Get rid of all spaces from the value field
         value[i]=f[2]+0
         # Determine the width for this column based on the width
         # of the title text plus 3 for spacing. Left-justify (%-).
         fmt[i]="%-"(length(title[i])+3)"s"
     }
     # We will want to sort on the key column so we need to create a
     # string at the start of each line to sort on later. Take the key
     # columns value and pad it with zeros up to 20 chars followed by
     # a space to separate it fromthe first real column. Conversion of
     # "7" to "0007" and "17" to "0017" is necessary because asort()
     # is alphabetical not numerical so all numeric fields must be the
     # same width to compare alphabetically.
     lines[NR] = sprintf("%020s ",value[key])

     # Now add the real columns, formatted as determined earlier.
     for (i=1; i<=numflds; i++) {
         lines[NR] = lines[NR] sprintf(fmt[flds[i]], value[flds[i]])
     }
}
END {
     # Print the title line
     for (i=1; i<=numflds; i++) {
         printf fmt[flds[i]], title[flds[i]]
     }
     print ""
     # Sort the lines alphabetically, i.e. by the value of the key column
     # added above to the front of each line.
     asort(lines)
     # Print each line
     for (i=1; i<=NR; i++) {
         # strip out the first numeric value, the key value added above
         sub("[[:digit:]]* ","",lines[i])
         print lines[i]
     }
}'

Regards,

	Ed.