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 first line value in second line and second line value in third line an

Discussion in 'SQL PL/SQL' started by jagadekara, Jul 3, 2015.

  1. jagadekara

    jagadekara Forum Guru

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

    I have a requirement like below.

    select * from my_tab;
    col1 col2 col3
    1 test1 01-jan-2014
    2 test2 02-jan-2013
    3 test5 09-feb-2015

    I need ti display like below.

    col1 col2 col3 req_col
    -----------------------------------------------------
    1 test1 01-jan-2014
    2 test2 02-jan-2013 01-jan-2014
    3 test5 09-feb-2015 02-jan-2013

    So how to do it?

    If it's not possible with sql then suggest me to do in xml peblisher (in rdf or in rtf)
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Re: How to display first line value in second line and second line value in third lin

    Hi Jagadekara,

    This is a case for analytic functions; try the following:
    Code (SQL):
    SELECT col1, col2, col3,
      lag(to_char(col3,'dd-mon-yyyy'), 1, NULL) OVER (ORDER BY col1) req_col  FROM my_tab
    ;
     
    jagadekara likes this.