Display Row to Columns Pattern

Shell Programming and Scripting
1

Hi All,
I have following pattern of output

 Number of executions               = 1
 Number of compilations             = 1
 Total execution time (sec.microsec)= 0.263898
 Statement text                     = ALTER TABLE DSSSTG.SG2_MIB_MIBP04 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
 Number of executions               = 1
 Number of compilations             = 1
 Total execution time (sec.microsec)= 0.173239
 Statement text                     = SELECT FILE_NM,SRC_STM_ID,MSR_PRD_TP,PRV_EXTR_TMST,MSR_PRD,MSR_PRD_DT FROM DSSSTG.V_SG_CTL1 V_SG_CTL1 WHERE SRC_STM_ID = ?AND FILE_NM='DSCLQ17.DAT'
Number of executions               = 4
 Number of compilations             = 1
 Total execution time (sec.microsec)= 0.011126
 Statement text                     = SELECT partitionmap FROM sysibm.systables, sysibm.syspartitionmaps WHERE sysibm.systables.pmap_id = sysibm.syspartitionmaps.pmap_id AND sysibm.systables.name='SG_SRC_X_AR' AND sysibm.systables.creator='DSSSTG  ' FOR READ ONLY

Expected Output as following

No Exec     No Compilations       Total execution time    Statement Text
--------    ---------------        -------------------    ---------------
1              1                           0.263898                    ALTER TABLE DSSSTG.SG2_MIB_MIBP04 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

1              1                           0.173239                    SELECT FILE_NM,SRC_STM_ID,MSR_PRD_TP,PRV_EXTR_TMST,MSR_PRD,MSR_PRD_DT FROM DSSSTG.V_SG_CTL1 V_SG_CTL1 WHERE SRC_STM_ID = ?AND FILE_NM='DSCLQ17.DAT'

4              1                           0.011126                     SELECT partitionmap FROM sysibm.systables, sysibm.syspartitionmaps WHERE sysibm.systables.pmap_id = sysibm.syspartitionmaps.pmap_id AND sysibm.systables.name='SG_SRC_X_AR' AND sysibm.systables.creator='DSSSTG  ' FOR READ ONLY

Thank You for all your contribution.

2

How about this:

awk '
{
  col=$0
  line=$0
  gsub(/ *= .*/, "", col)
  gsub(/^[^=]*= /, "", line)
  if (!(col in COLS)) {
      head=head OFS col
      TITLE[++cols]=col
      COLS[col]=cols
  }
  if (COLS[col]==1) ROW++
  v[ROW,COLS[col]]=line
}
END {
  print substr(head,2)
  gsub("[^"OFS"]", "-", head)
  print substr(head,2)
  for(r=1; r<=ROW; r++) {
      printf("%-*s", length(TITLE[1]), v[r, 1])
      for(c=2; c<=cols; c++)
        printf("%s%-*s", OFS, length(TITLE[c]), v[r,c])
      print ""
  }
}
' OFS='\t' infile
2 Likes
3

Here is another way to do it assuming that you always have 4 input lines / output line and that the input lines are always in the same relative order. It uses slightly different headings than you requested, but otherwise seems to produce output similar to what you seem to want:

#!/bin/ksh
printf "# Exec # Comps Execution time Statement Text\n%s\n" \
       "------ ------- -------------- --------------"
i=0
while IFS== read -r junk st
do      case $i in
        (0)     printf "%6s" "$st";;
        (1)     printf "%8s" "$st";;
        (2)     printf "%15s" "$st";;
        (3)     printf "%s\n" "$st";;
        esac
        i=$((++i % 4))
done < file

This was tested with the Korn shell on Mac OS X, but will work with any POSIX conforming shell (including bash and ksh).

If you want different headings and different alignment, this should at least serve as a template for one way to reformat your output.

The output produced by this script with your sample input is:

# Exec # Comps Execution time Statement Text
------ ------- -------------- --------------
     1       1       0.263898 ALTER TABLE DSSSTG.SG2_MIB_MIBP04 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
     1       1       0.173239 SELECT FILE_NM,SRC_STM_ID,MSR_PRD_TP,PRV_EXTR_TMST,MSR_PRD,MSR_PRD_DT FROM DSSSTG.V_SG_CTL1 V_SG_CTL1 WHERE SRC_STM_ID = ?AND FILE_NM='DSCLQ17.DAT'
     4       1       0.011126 SELECT partitionmap FROM sysibm.systables, sysibm.syspartitionmaps WHERE sysibm.systables.pmap_id = sysibm.syspartitionmaps.pmap_id AND sysibm.systables.name='SG_SRC_X_AR' AND sysibm.systables.creator='DSSSTG  ' FOR READ ONLY
2 Likes
4

Awesome

5

Thanks for the sharing..your input is very much great and awesome!