HELP NEED CROSSTABS WITH INFORMIX

> > If you select items with the group by clause, you get them back in > vertical order i.e. > > item1 amount1 > item2 amount2 > . > . > itemn amountn > > Does anybody know an utility to get it horizontal? i.e. > > item1 item2 ... itemn > amount1 amount2 ... amountn > > Is there an easier way as programme it with 4GL i.e. ?? > > We use INFORMIX ONLINE 5.0 on SCO 3.2 Dave Cortesi posted this some time ago: From: cortesi@informix.com (David Cortesi) Subject: Repost: making isql output horizontal Date: 5 Jan 93 23:30:08 GMT Reply-To: cortesi@informix.com (David Cortesi) The version of this program posted earlier had 1 and 1/2 bugs, which are fixed in the following. Also note that "nawk" as distributed by Sun is suitable for executing this. # # reform.isql.output.awk # # An awk program to reformat an Informix-ISQL output listing, # changing it from vertical format to horizontal report format. # # Usage: # gawk -f invsql [dpat="data-pattern"] [lpp=n] [hpat="head-pattern"] # # <data-pattern> is a printf() pattern to format the column values # <n> is the number of lines of data per page # <head-pattern> is a printf() pattern to format the column headings # # Headings are printed only when lpp is specified as greater than zero. # See below for default heading and data patterns. # # As written, supports only 30 columns of output. See the end of the # program for how to expand this to more columns if required. # # Requires an "awk" that matches the book by Aho et.al, that is, # Gnu awk or SunOS "nawk" -- not the obsolete awk shipped by Sun, NeXT, etc. # # Author: David Cortesi (cortesi@informix.com) # # --------------------- User's Guide (wysiwig!) -------------------------- # # Standard input to invsql is an ISQL vertical-format report like this: # # order_num 1007 # order_date 03/25/1989 # customer_num 117 # backlog n # po_num 278693 # ship_date 04/23/1989 # ship_weight 125.90 # # We change it to horizontal format with optional page headings: # # order_num order_date customer_num backlog po_num ship_date ship_weight # 1007 03/25/1989 117 n 278693 04/28/1989 125.90 # 1012 06/05/1989 117 n 278701 06/09/1989 70.80 # # The program collects the column values from a group of input lines, # then prints one output line using a printf() like this: # printf(pattern,col1,col2,...,coln) # where each "col" is the string value of that column from the input. # # The default pattern is: "%nns %nns...\\n" where each "nn" is the # default width of that column, which is: the larger of the width of # the heading text for that column, and the width of the data in that # column in the very first input group. # # The default is often wrong, but you can specify exact widths, and # control the format in many other ways, by specifying a printf() # pattern string as the command-line argument dpat="pattern". # # The program can print column headings at the top of each page of # data. The default is to NOT print headings -- you can paginate # the output using the pr(1) command for example. However if you # specify lpp=n, n>0, the program will print column headings before # each group of n data lines. # # The default column heading display is: # printf("\\f%nns %nns...\\n\\n",col1,col2...coln) # where each "col" is the heading text of that column from the first # input group, and the "nn" values are as for the data pattern. # You can supply your own pattern using hpat="pattern" on the command line. # # When writing printf patterns as part of c-shell commands you need # only write the string in quotes, like this: dpat="%-5d\\t%20f\\n" # (The c shell does not object to backslashes in such quotes.) # # version of 1/4/93 with a bug fixed # --------------------- The Program -------------------------- BEGIN { state = 0; colno = 0; recno = 0 } # Leading blank lines: ignore them state == 0 && $1 == "" { next } # First data line of first group: note the length of the prefix # (the total width of heading and spaces), which is the same on each line, # even when the data value on a line is null. state == 0 && $1 != "" { match($0,/^[^ ]+ */) pfxlen = RLENGTH+1 state = 1 # now into first data record } # Any data line of any group: save the string value of the data line, # which is the whole line to the right of the prefix. Data is saved # in the array v[1..n]. $1 != "" { v[++colno] = substr($0,pfxlen) } # Any data line of the first data record: save the column name # as well, and set the default length of this column. Column # names are saved in array h[1..n], and lengths in dlen[1..n]. state == 1 && $1 != "" { h[colno] = $1 lh = length($1) lv = length(v[colno]) dlen[colno] = (lh > lv)?lh:lv } # End of first input group (empty line in state==1): build the # default print patterns, or use the supplied ones. state == 1 && $1 == "" { pl = 0 # no pagination if (lpp > 0) pl = lpp pd = "" for( j = 1; j <= colno; ++j ) pd = pd "%" dlen[j] "s " sub(" $","\\n",pd) ph = "\\f" pd "\\n" if (dpat != "") pd = fixup(dpat) if (hpat != "") { ph = fixup(hpat) if (pl == 0) pl = 60 } if (pl > 0) recno = pl-1 # force starting headings state = 2 # no more setting up to do } # End of any input group (empty line): print. $1 == "" { do_output() } # End of input file: print if the last line wasn't a blank END { do_output() } # Output process: print accumulated column values horizontally # and reset the column counter. function do_output() { if (colno > 0) { # some columns collected # the printf statements have to list every possible column 1..colno # Columns that do not exist generate no output (because there's no # format for them in the pattern :) and awk does not object to you # referring to empty array elements. To support more columns, add # more lines to the printf statements below, following the same pattern. if ( ++recno == pl ) { # only succeeds when pl > 0 printf ph \\ ,h[1],h[2],h[3],h[4],h[5],h[6],h[7],h[8],h[9],h[10] \\ ,h[11],h[12],h[13],h[14],h[15],h[16],h[17],h[18],h[19],h[20] \\ ,h[21],h[22],h[23],h[24],h[25],h[26],h[27],h[28],h[29],h[30] recno = 0 } printf pd \\ ,v[1],v[2],v[3],v[4],v[5],v[6],v[7],v[8],v[9],v[10] \\ ,v[11],v[12],v[13],v[14],v[15],v[16],v[17],v[18],v[19],v[20] \\ ,v[21],v[22],v[23],v[24],v[25],v[26],v[27],v[28],v[29],v[30] colno = 0 } } # As received from