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 display a line for each row in sql?

Discussion in 'SQL PL/SQL' started by jagadekara, Aug 8, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I just want to know Is it possible to display a line for each row of out put in SQL.

    select ename from emp;

    ENAME
    -------
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS
    JAMES
    FORD
    MILLER


    But I need to display like this....

    ENAME
    -------
    SMITH
    -------
    ALLEN
    -------
    WARD
    -------
    JONES
    -------
    MARTIN
    -------
    BLAKE
    -------
    CLARK
    -------
    SCOTT
    -------
    KING
    -------
    TURNER
    -------
    ADAMS
    -------
    JAMES
    -------
    FORD
    -------
    MILLER
    -------

    Note that it's just for gaining knowledge, not for client requirement....
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    one of options....
    simply use cross-join

    Code (SQL):

    WITH
    emp AS (
    SELECT 'SMITH' ename FROM dual UNION ALL
    SELECT 'ALLEN' FROM dual UNION ALL
    SELECT 'WARD' FROM dual
    )
     
    SELECT decode(z.id,2,'--------',e.ename) ename FROM emp e
    CROSS JOIN (SELECT level id FROM dual CONNECT BY level < 3 ) z
    ORDER BY e.ename,z.id;

     
    Code (SQL):

    WITH
    emp AS (
    SELECT 'SMITH' ename FROM dual UNION ALL
    SELECT 'ALLEN' FROM dual UNION ALL
    SELECT 'WARD' FROM dual
    )
     ,htab (ename,i) AS
     (SELECT ename, 1 i FROM emp
      UNION ALL
      SELECT ename,i+1 FROM htab
      WHERE i < 2
      )
      SELECT decode(i,2,'-----',h.ename) ename FROM htab h
      ORDER BY h.ename,h.i;
     


     
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Sergey,

    First Query is fine. But How to do it with out hard coding?

    Second query giving error as unsupported column aliasing
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    first query:
    Code (SQL):

    SELECT decode(z.id,2,'--------',e.ename) ename
    FROM emp e
    CROSS JOIN (SELECT level id FROM dual CONNECT BY level < 3 ) z
    ORDER BY e.ename,z.id;
     
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Try this:

    Code (Text):
    SELECT email
    FROM
    (
    SELECT email, employee_id, 1
    FROM   hr.employees
    UNION
    SELECT '---------', employee_id, 2
    FROM   hr.employees
    ORDER BY 2, 3
    )
    WHERE  rownum < 11

    EMAIL                  
    -------------------------
    SKING                    
    ---------                
    NKOCHHAR                  
    ---------                
    LDEHAAN                  
    ---------                
    AHUNOLD                  
    ---------                
    BERNST                    
    ---------
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Second query works for me in 11.2.0.3; which release of Oracle are you using, and did you copy the query text correctly?
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Sergey... This is working fine.

    Also thanks to ocprep your's also working fine
     
  8. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi David,

    My version details....

    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

    I copied correctly.