Forum Stats

  • 3,770,127 Users
  • 2,253,073 Discussions
  • 7,875,336 Comments

Discussions

Need sql query of this requirements

jaijeeva
jaijeeva Member Posts: 94 Blue Ribbon

Hi,

Please help to form the query for the below expected results. working on 19c version. I have given sample table ,data & expected results.

 create table Author (

   id number not null,

   name varchar2(30) not null

 );


insert into Author values (1, 'jai');

insert into Author values (2, 'Ritesh');

insert into Author values (3, 'Kaarki');

insert into Author values (4, 'Sindhu');

insert into Author values (6, 'Liver');

commit;

    id | name

 ---------+----------------

     1 | jai

     2 | Ritesh

     3 | Kaarki

     4 | Sindhu

     6 | Liver


expected results as below


  left_name | right_name

 ------------+----------------

     NULL | jai

    Ritesh | Kaarki

    Sindhu | NULL

    Liver | NULL

Tagged:

Comments

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi, @jaijeeva

    Thanks for posting the sample data and results; that's very helpful.

    Don't forget to explain how you get those results from that data. What does each row of the output represent? What distinguishes a "left name" from a "right_name"?

  • mathguy
    mathguy Member Posts: 10,164 Blue Diamond

    Yay! Finally someone counts from 0, not from 1!


    select left_name, right_name
    from   (select trunc(id/2) as rn, mod(id, 2) as col, name from author)
    pivot  (min(name) for col in (0 as left_name, 1 as right_name))
    order  by rn
    ;
    
    
    LEFT_NAME  RIGHT_NAME
    ---------- ----------
               jai       
    Ritesh     Kaarki    
    Sindhu               
    Liver        
    
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,596 Red Diamond

    Yay! Finally someone counts from 0, not from 1!

    Hehehe.. 😁