1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to add a new line character to SQL Query

Discussion in 'SQL PL/SQL' started by Farshad Javadi, Dec 11, 2009.

  1. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Hello all,

    I have the following query that produces output horizentally. How can I add new-line character (or next-line) character, so that my output becomes vertcally instead of horizentally.

    With respect,
    Farshad Javadi

    SELECT TO_CHAR(add_months(SYSDATE, 0),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -1),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -2),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -3),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -4),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -5),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -6),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -7),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -8),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -9),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -10),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -11),'MON-YY'),
    TO_CHAR(add_months(SYSDATE, -12),'MON-YY')
    FROM dual
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Rewrite this as a UNION query. To illustrate your query is shown first, and the UNION rewrite is shown after:

    Code (SQL):
    SQL> COLUMN this_month format a10
    SQL> COLUMN last_month format a10
    SQL> COLUMN two_months_ago format a10
    SQL> COLUMN three_months_ago format a10
    SQL> COLUMN four_months_ago format a10
    SQL> COLUMN five_months_ago format a10
    SQL> COLUMN six_months_ago format a10
    SQL> COLUMN seven_months_ago format a10
    SQL> COLUMN eight_months_ago format a10
    SQL> COLUMN nine_months_ago format a10
    SQL> COLUMN ten_months_ago format a10
    SQL> COLUMN eleven_months_ago format a10
    SQL> COLUMN one_year_ago format a10
    SQL> SET linesize 150 trimspool ON
    SQL>
    SQL> SELECT TO_CHAR(add_months(SYSDATE, 0),'MON-YY') this_month,
      2  TO_CHAR(add_months(SYSDATE, -1),'MON-YY') last_month,
      3  TO_CHAR(add_months(SYSDATE, -2),'MON-YY') two_months_ago,
      4  TO_CHAR(add_months(SYSDATE, -3),'MON-YY') three_months_ago,
      5  TO_CHAR(add_months(SYSDATE, -4),'MON-YY') four_months_ago,
      6  TO_CHAR(add_months(SYSDATE, -5),'MON-YY') five_months_ago,
      7  TO_CHAR(add_months(SYSDATE, -6),'MON-YY') six_months_ago,
      8  TO_CHAR(add_months(SYSDATE, -7),'MON-YY') seven_months_ago,
      9  TO_CHAR(add_months(SYSDATE, -8),'MON-YY') eight_months_ago,
     10  TO_CHAR(add_months(SYSDATE, -9),'MON-YY') nine_months_ago,
     11  TO_CHAR(add_months(SYSDATE, -10),'MON-YY') ten_months_ago,
     12  TO_CHAR(add_months(SYSDATE, -11),'MON-YY') eleven_months_ago,
     13  TO_CHAR(add_months(SYSDATE, -12),'MON-YY') one_year_ago
     14  FROM dual
     15  /

    THIS_MONTH LAST_MONTH TWO_MONTHS THREE_MONT FOUR_MONTH FIVE_MONTH SIX_MONTHS SEVEN_MONT EIGHT_MONT NINE_MONTH TEN_MONTHS ELEVEN_MON ONE_YEAR_A
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    DEC-09     NOV-09     OCT-09     SEP-09     AUG-09     JUL-09     JUN-09     MAY-09     APR-09     MAR-09     FEB-09     JAN-09     DEC-08

    SQL>
    SQL> SELECT TO_CHAR(add_months(SYSDATE, 0),'MON-YY') this_month
      2  FROM dual UNION
      3  SELECT TO_CHAR(add_months(SYSDATE, -1),'MON-YY') last_month
      4  FROM dual UNION
      5  SELECT TO_CHAR(add_months(SYSDATE, -2),'MON-YY') two_months_ago
      6  FROM dual UNION
      7  SELECT TO_CHAR(add_months(SYSDATE, -3),'MON-YY') three_months_ago
      8  FROM dual UNION
      9  SELECT TO_CHAR(add_months(SYSDATE, -4),'MON-YY') four_months_ago
     10  FROM dual UNION
     11  SELECT TO_CHAR(add_months(SYSDATE, -5),'MON-YY') five_months_ago
     12  FROM dual UNION
     13  SELECT TO_CHAR(add_months(SYSDATE, -6),'MON-YY') six_months_ago
     14  FROM dual UNION
     15  SELECT TO_CHAR(add_months(SYSDATE, -7),'MON-YY') seven_months_ago
     16  FROM dual UNION
     17  SELECT TO_CHAR(add_months(SYSDATE, -8),'MON-YY') eight_months_ago
     18  FROM dual UNION
     19  SELECT TO_CHAR(add_months(SYSDATE, -9),'MON-YY') nine_months_ago
     20  FROM dual UNION
     21  SELECT TO_CHAR(add_months(SYSDATE, -10),'MON-YY') ten_months_ago
     22  FROM dual UNION
     23  SELECT TO_CHAR(add_months(SYSDATE, -11),'MON-YY') eleven_months_ago
     24  FROM dual UNION
     25  SELECT TO_CHAR(add_months(SYSDATE, -12),'MON-YY') one_year_ago
     26  FROM dual
     27  /

    THIS_MONTH
    ----------
    APR-09
    AUG-09
    DEC-08
    DEC-09
    FEB-09
    JAN-09
    JUL-09
    JUN-09
    MAR-09
    MAY-09
    NOV-09

    THIS_MONTH
    ----------
    OCT-09
    SEP-09

    13 ROWS selected.

    SQL>
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Or using connect by ...

    Code (SQL):

    SQL> SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1*(LEVEL-1)),'MON-YY')
      2   FROM DUAL CONNECT BY LEVEL<=13;

    TO_CHA
    ------
    DEC-09
    NOV-09
    OCT-09
    SEP-09
    AUG-09
    JUL-09
    JUN-09
    MAY-09
    APR-09
    MAR-09
    FEB-09
    JAN-09
    DEC-08

    13 ROWS selected.

    SQL>

     
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Im getting only one value, when i tried this
    Code (SQL):
    SQL> SELECT LEVEL,TO_CHAR(ADD_MONTHS(SYSDATE,-1*(LEVEL-1)),'MON-YY')
      2  FROM DUAL CONNECT BY LEVEL<=13;

         LEVEL TO_CHA
    ---------- ------
             1 NOV-10

    SQL>
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which release of Oracle are you using? That functionality works in 10g and later releases, not in 9i and earlier versions. From 10.2.0.4:

    Code (SQL):
     
    SQL> SELECT LEVEL,TO_CHAR(ADD_MONTHS(SYSDATE,-1*(LEVEL-1)),'MON-YY')
    FROM DUAL CONNECT BY LEVEL<=13;
      2

         LEVEL TO_CHAR(ADD_
    ---------- ------------
             1 NOV-10
             2 OCT-10
             3 SEP-10
             4 AUG-10
             5 JUL-10
             6 JUN-10
             7 MAY-10
             8 APR-10
             9 MAR-10
            10 FEB-10
            11 JAN-10
         LEVEL TO_CHAR(ADD_
    ---------- ------------
            12 DEC-09
            13 NOV-09
     
    13 ROWS selected.
     
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hmm..... I am using 9i version.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This should work even in Oracle 9i.

    Code (SQL):
    SELECT * FROM (
    SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1*(LEVEL-1)),'MON-YY')
    FROM DUAL CONNECT BY LEVEL<=13)