How get table format output in vertical rather than horizontal?

In Informix if I do SELECT * FROM tableA; The output format will be look like: col1 val11 col2 val12 .... coln val1n col1 val21 col2 val22 .... coln val2n n rows retrieved. But how do I get the table output format look like: col1 col2 ..... coln val11 val12 ..... val1n val21 val22 .... val2n n rows retrieved. Thank you! -- Why we want to teach our babies to talk and walk, then later we tell them "sit down!", "be quiet!" ? Democracy is not a better way for a solution, it is just another way to spread the blames. --Raymond

Raymond Chui wrote: > > In Informix if I do > > SELECT * FROM tableA;> > The output format will be look like: > > col1 val11 > col2 val12 > .... > coln val1n > > col1 val21 > col2 val22 > .... > coln val2n > > n rows retrieved. > > But how do I get the table output format look like: > > col1 col2 ..... coln > > val11 val12 ..... val1n > val21 val22 .... val2n > > n rows retrieved. Dbaccess automatically does this if the output cannot fit in 80 columns. There is no way to tell dbaccess to modify this behavior. You have three good options: 1) Use a script to read the dbaccess output and reformat it. You can either write the beast yourself or download one of the ones available from the IIUG Software Repository. 2) Use the dbaccess UNLOAD verb to output to delimited format the use a perl or awk script to read that file and reformat the output. This is trivial and you may want to just write this one yourself. 3) Get Jonathan Leffler's sqlcmd package from the IIUG Repository. It is a replacement for dbaccess that normally outputs delimited format and you can tell it to use spaces for the delimiter and do some other output formatting. Art S. Kagel

Well, I suppose I could brag a bit about the dbaccess replacement of 4glworks, which does give you a choice of vertical and horizontal formatting, and has a couple of nifty features like allowing you to format data with 4gl or c specifiers, or storing the output in text variables (which you can then use from within 4gl applications). have a look at my sig... -- Ciao, Marco ______________________________________________________________________________ Marco Greco <marco@informix.com> Standard disclaimers apply! Informix faq http://www.iiug.org/techinfo/faq/informix.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm "Art S. Kagel" wrote: > > Raymond Chui wrote: > > > > In Informix if I do > > > > SELECT * FROM tableA;> > > > The output format will be look like: > > > > col1 val11 > > col2 val12 > > .... > > coln val1n > > > > col1 val21 > > col2 val22 > > .... > > coln val2n > > > > n rows retrieved. > > > > But how do I get the table output format look like: > > > > col1 col2 ..... coln > > > > val11 val12 ..... val1n > > val21 val22 .... val2n > > > > n rows retrieved. > > Dbaccess automatically does this if the output cannot fit in 80 columns. > There is no way to tell dbaccess to modify this behavior. You have three > good options: > > 1) Use a script to read the dbaccess output and reformat it. You can either > write the beast yourself or download one of the ones available from the > IIUG Software Repository. > 2) Use the dbaccess UNLOAD verb to output to delimited format the use a > perl or awk script to read that file and reformat the output. This is > trivial and you may want to just write this one yourself. > 3) Get Jonathan Leffler's sqlcmd package from the IIUG Repository. It is > a replacement for dbaccess that normally outputs delimited format and > you can tell it to use spaces for the delimiter and do some other output > formatting. > > Art S. Kagel

Raymond. I dont think you can do that using isql or dbaccess but if its 4GL you can use after group to do this. You would gather the records in the report and pass each record to formatter. The formatter would process only on last row control block Sameer To Produce a Matrix report requires that you artificially flag the output records so that you print the output in a froup block statement.(after group of / on last row) Raymond Chui <raymond.chui@noaa.gov> wrote in message news:392D3895.2D343EF7@noaa.gov... > In Informix if I do > > SELECT * FROM tableA;> > The output format will be look like: > > col1 val11 > col2 val12 > .... > coln val1n > > col1 val21 > col2 val22 > .... > coln val2n > > > > n rows retrieved. > > But how do I get the table output format look like: > > col1 col2 ..... coln > > val11 val12 ..... val1n > val21 val22 .... val2n > > n rows retrieved. > > Thank you! > > > > -- > Why we want to teach our babies to talk and walk, > then later we tell them "sit down!", "be quiet!" ? > > Democracy is not a better way for a solution, > it is just another way to spread the blames. > > --Raymond > >

Hi Raymond, I had to solve this problem sometime ago and I found the solution in the www.iiug.org software section. It's an awk utility called "reform_sql". It's really good stuff! I used it a few times and it works really well. You will have to run the SQL output to a file, and then run the reform utility on the output file to get the desired results. I don't believe that you can pipe the SQL output directly to the utility though. Samuel Lee. Trinidad & Tobago Electricity Commission. In article <392D3895.2D343EF7@noaa.gov>, Raymond Chui <raymond.chui@noaa.gov> wrote: > This is a multi-part message in MIME format. > --------------836C1CAD6C6BBA9CCFAE4D64 > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > In Informix if I do > > SELECT * FROM tableA;> > The output format will be look like: > > col1 val11 > col2 val12 > .... > coln val1n > > col1 val21 > col2 val22 > .... > coln val2n > > n rows retrieved. > > But how do I get the table output format look like: > > col1 col2 ..... coln > > val11 val12 ..... val1n > val21 val22 .... val2n > > n rows retrieved. > > Thank you! > > -- > Why we want to teach our babies to talk and walk, > then later we tell them "sit down!", "be quiet!" ? > > Democracy is not a better way for a solution, > it is just another way to spread the blames. > > --Raymond > > --------------836C1CAD6C6BBA9CCFAE4D64 > Content-Type: text/x-vcard; charset=us-ascii; > name="raymond.chui.vcf" > Content-Transfer-Encoding: 7bit > Content-Description: Card for Raymond Chui > Content-Disposition: attachment; > filename="raymond.chui.vcf" > > begin:vcard > n:Chui;Raymond > tel;fax:(301)713-0963 > tel;home:ICQ #: 16722494 > tel;work:(301)713-0624 Ext. 168 > x-mozilla-html:TRUE > url:http://members.xoom.com/rchui/ > org:NWS, NOAA > version:2.1 > email;internet:Raymond.Chui@noaa.gov > title:SA, DBA > adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, W/OH2=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910- 3283;U.S.A. > x-mozilla-cpt:;30256 > fn:Raymond Chui > end:vcard > > --------------836C1CAD6C6BBA9CCFAE4D64-- > > Sent via Deja.com http://www.deja.com/ Before you buy.