Forum Stats

  • 3,837,059 Users
  • 2,262,222 Discussions
  • 7,900,194 Comments

Discussions

Categories

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:
· Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 728534
    728534 Member Posts: 1,386
    Hi,
    Nice one!!!

    Cheers!!!
    Bhushan
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
This discussion has been closed.