Home » SQL & PL/SQL » SQL & PL/SQL » address format (oracle 9i)
address format [message #343126] Tue, 26 August 2008 06:24 Go to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
SQL> select * from address;

        ID NAME       ADD1       ADD2       CITY       STATE             PIN
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 kaushal    sakarpur   dakat      faribis    ddn            123249
         2 karan      kararpur   katar      bisla      ghat           765249


now i want report like
         1     kaushal 
               sakarpur
               dakat
               faribis
               ddn 
              123249
         2     karan
               kararpur
               katar
               bisla
               ghat
              765249


please help me to do so.


regards,
kaushal

[Updated on: Tue, 26 August 2008 06:27]

Report message to a moderator

Re: address format [message #343129 is a reply to message #343126] Tue, 26 August 2008 06:33 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Use DECODE function. Try.
If you are not being able to figure out then,

Such types of questions has been asked here so many times. Please Search here by "pivot".



Regards,
Oli

[Updated on: Tue, 26 August 2008 06:34]

Report message to a moderator

Re: address format [message #343338 is a reply to message #343126] Wed, 27 August 2008 00:15 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT * FROM address
  2  /

        ID NAME       ADD1       ADD2       CITY       STATE        PIN
---------- ---------- ---------- ---------- ---------- ----- ----------
         1 kaushal    sakarpur   dakat      faribis    ddn       123249
         2 karan      kararpur   katar      bisla      ghat      765249

SCOTT@orcl_11g> COLUMN name_and_address FORMAT A16
SCOTT@orcl_11g> SELECT id,
  2  	    name || chr(10) ||
  3  	    add1 || chr(10) ||
  4  	    add2 || chr(10) ||
  5  	    city || chr(10) ||
  6  	    state || chr(10) ||
  7  	    pin as name_and_address
  8  FROM   address
  9  ORDER  BY id
 10  /

        ID NAME_AND_ADDRESS
---------- ----------------
         1 kaushal
           sakarpur
           dakat
           faribis
           ddn
           123249

         2 karan
           kararpur
           katar
           bisla
           ghat
           765249


SCOTT@orcl_11g>

Previous Topic: Database Trigger
Next Topic: single column data
Goto Forum:
  


Current Time: Sun Dec 11 04:12:44 CST 2016

Total time taken to generate the page: 0.10200 seconds