Path: utzoo!attcan!uunet!bu.edu!dartvax!eleazar.dartmouth.edu!carl
From: carl@eleazar.dartmouth.edu (Carl Pedersen)
Newsgroups: comp.databases
Subject: Re: Oracle Question
Message-ID: <23372@dartvax.Dartmouth.EDU>
Date: 26 Jul 90 18:29:28 GMT
References: <8601@linus.SLCS.SLB.COM> <11845@hydra.gatech.EDU>
Sender: news@dartvax.Dartmouth.EDU
Lines: 43

re4@prism.gatech.EDU (RUSSELL EARNEST) writes:
>In article <8601@linus.SLCS.SLB.COM>, susan@roadrunner.slcs.slb.com (Susan Rosenbaum) writes:
>> I have an Oracle database containing address information.  What I want
>> to do is print the addresses onto two-column formatted mailing labels.
>> For example,
>> 	Joe Smith			Jane Jones
>> 	111 Main St.			222 First St.
>> 	Yourtown, Tx. 99999 		Yourcity, Tx. 88888
>> Does anyone know the magic incantation from SQL*Plus to format the
>> responses from a database query in this manner?  Thanks!

>I think what you are looking for is in the SQL*Report Writer package not
>SQL*Plus.

Well, I suppose SQL*ReportWriter could handle this, but it costs
a lot of money.  SQL*Report, which you get for free can also do
a pretty good job, but you have to learn another language.  You
CAN do it in plain old SQL*Plus if you are willing to be
obscure.  Something like the following seems to work for me:

 SQL> set pagesize 0;
 SQL> column line2 newline;
 SQL> column line3 newline;
 SQL> r
   1  select rpad(max(decode(mod(rownum,2),1,name)),30)||
   2         max(decode(mod(rownum,2),0,name)) line1,
   3         rpad(max(decode(mod(rownum,2),1,street)),30)||
   4         max(decode(mod(rownum,2),0,street)) line2,
   5         rpad(max(decode(mod(rownum,2),1,city_st_zip)),30)||
   6         max(decode(mod(rownum,2),0,city_st_zip)) line3
   7    from folks
   8*  group by trunc((rownum-1)/2)

Amazing what you can do with a language that doesn't even have
primitive recursion, isn't it?  You can also do a reflexive join
on rownum to achieve more or less the same thing.  Of course, all
of this is highly non-ANSII, but most of what you need can be
built up using only ANSII constructs if that's a problem.

I was a little surprised that ORACLE let me use ROWNUM in a GROUP
BY clause.  ROWNUM is not stable.  Newer versions of ORACLE may
not support this.  However, it's easy enough to add a sequence
number to your table and use that instead.