From: Isaac Loven <yitz@tlv.cig.mot.com>
To: software@iiug.org
Subject: Too many fields, sql output in rows, not columns
Date: Sun, 4 Jul 1999 08:18:49 +0200 

 When too many fields are selected in an SQL query, the output is displayed
 as rows and not in a nice table format.
 
 The PERL program below reformats SQL output from columns to rows.
 
 If the output is already in columns, no change is made. ( transparent )
 
 The output table will have the fields TAB delimited.  Nulls will be printed
 as space or zero. ( you choose )
 
 Save this file as  "col2rows.pl"   ( or what ever you wish ) .
 
 make it excutable :  chmod +x  col2rows.pl
 to run do :
 
 dbaccess  my_database_name  my_sql.sql | col2rows.pl
  ( The output of the sql is piped through col2rows.pl  ) 
 
 Here the output is saved to a file.
 dbaccess  my_database_name  my_sql.sql | col2rows.pl  > outputfile.txt
 
 I hope someone finds this helpful.  (  If you use this script, I would be
 pleased if you would email  me that you are using it. )
 
 Have Fun.
 Isaac.
 
 
============================================================================
#!/usr/local/bin/perl   ### this Must be the first line of your executable file.

# col2rows.pl

# By Isaac Loven isaac_loven@hotmail.com   June 1999

# When too many fields from an Informix Database are selected in an SQL query, 
# the output is displayed as rows and not in a nice table format. 

# This program reformats SQL output from columns to rows.

# If the output is already in columns, no change is made.

# The first line is the location of Your perl interpreter. 
# You may have to change this to the location of your perl  
# ie   #!/usr/local/perl



# If a field is Null,  choose what character you wish will replace it: 
# choose fill value:
# $fill=" ";       ## if value is blank print a space.  I prefer a 0 for my applications, 
  $fill=0;         ## if value is blank print 0.


while($in=<>)   # read one line in std buffer 
{	$line++;
	if ( $line == 3 )
		{
   		if ( $in  =~ /\w/ )  
				{  $passthrough = "Y";
					print "\n";
					print "\n";
				}
			else
				{  $passthrough="N";
					print "\n";
				}
		}

	if (  $passthrough  eq "Y" ) {  print $in ; }
	if (  $passthrough  eq "N" ) 
		{
   		( $name , $value) = split ('\s+', $in);  # split std buffer 
			if ( $name ne  ""  ) 
				{
				$value=$fill if ( $value eq "" );    
				###### Now if the data id a fraction ie 3.123456778  Cut off 
				###### the least significant digits after the 3rd decimal place:
				$value =~s/(\d+\.\d\d\d)\d+/$1/;  
				$val_str .= $value ."\t";
				$header  .= $name . "\t"; 
				}
   	   	 if ( $name eq ""  &&   $blank==1     )  { print "$header\n\n";  }  
   	   	 if ( $name eq "" ) { print "$val_str\n";  $blank++; $val_str="";    }
		}
}