Forum Stats

  • 3,839,685 Users
  • 2,262,529 Discussions
  • 7,901,038 Comments

Discussions

unable to create Query ? (Interesting Scenerio)

569986
569986 Member Posts: 227
edited Dec 7, 2009 5:47AM in SQL & PL/SQL
Hi all, i am unable to create a query for below expected output with the following data. Any help will be appreciated?


Expected output

ID1 NAME1 ID2 NAME2
1 test1 2 test2
3 test3 4 test4
5 test5 6 test6



Thanks in Advance
Tagged:

Best Answer

  • 730428
    730428 Member Posts: 2,087
    edited Dec 2, 2009 5:06AM Answer ✓
    Select o.id id1, o.name name1, e.id id2, e.name name2
      from emp o, emp e
     where o.id+1=e.id(+)
        and mod(o.id,2)=1;
    Odd id on column1 and following even id on column2.

    Max

Answers

  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072
    with tab as 
    (
      select 1 col1, 'test1' col2 from dual union all
      select 2 col1, 'test2' col2 from dual union all
      select 3 col1, 'test3' col2 from dual union all
      select 4 col1, 'test4' col2 from dual union all
      select 5 col1, 'test5' col2 from dual union all
      select 6 col1, 'test6' col2 from dual 
    ) 
     SELECT *
       FROM
      (SELECT col1                          ,
        col2                                ,
        lead(col1) over (order by col1) col3,
        lead(col2) over (order by col1) col4
         FROM tab
      )
      WHERE mod(col1,2)=1
    Ravi Kumar
    ravikumar.sv
  • 730428
    730428 Member Posts: 2,087
    edited Dec 2, 2009 5:06AM Answer ✓
    Select o.id id1, o.name name1, e.id id2, e.name name2
      from emp o, emp e
     where o.id+1=e.id(+)
        and mod(o.id,2)=1;
    Odd id on column1 and following even id on column2.

    Max
  • 728534
    728534 Member Posts: 1,386
    Hi,
    Nice one!!!

    Cheers!!!
    Bhushan
  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072
    with tab as 
    (
      select 1 col1, 'test1' col2 from dual union all
      select 2 col1, 'test2' col2 from dual union all
      select 3 col1, 'test3' col2 from dual union all
      select 4 col1, 'test4' col2 from dual union all
      select 5 col1, 'test5' col2 from dual union all
      select 6 col1, 'test6' col2 from dual 
    )  
     SELECT MAX(DECODE(mod(col1,2),1,col1)) col1,
      MAX(DECODE(mod(col1,2),1,col2)) col2      ,
      MAX(DECODE(mod(col1,2),0,col1)) col3      ,
      MAX(DECODE(mod(col1,2),0,col2)) col4
       FROM tab
    GROUP BY floor((col1-1)/2)
    It can be done in a single read on table without a join also like above... ;-)

    Ravi Kumar
    ravikumar.sv
  • 728534
    728534 Member Posts: 1,386
    I came up with the same solution of max(decode) but thought analytical approach was good also something new to learn if one does not know :)

    Cheers!!!
    Bhushan
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I used Pivot B-)
    However I think group by and max(decode is more simple.
    Because Pivot need calc to columns.
    with tab as(
    select 1 col1, 'test1' col2 from dual union all
    select 2 col1, 'test2' col2 from dual union all
    select 3 col1, 'test3' col2 from dual union all
    select 4 col1, 'test4' col2 from dual union all
    select 5 col1, 'test5' col2 from dual union all
    select 6 col1, 'test6' col2 from dual)
    select *
      from (select floor((col1-1)/2) as GID,mod(col1,2) as md,
            col1,col2
            from tab)
    Pivot(max(col1) as ID,max(Col2) as name for md in(1,0))
    order by GID;
    
    GID  1_ID  1_NAM  0_ID  0_NAM
    ---  ----  -----  ----  -----
      0     1  test1     2  test2
      1     3  test3     4  test4
      2     5  test5     6  test6
    http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/analysis.htm#BCFHHHHF
    If you need to pivot on an expression,
    then you should alias the expression in a view before the PIVOT operation.
    Aketi Jyuuzou
This discussion has been closed.