Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

unable to create Query ? (Interesting Scenerio)

569986Dec 2 2009 — edited Dec 7 2009
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
This post has been answered by 730428 on Dec 2 2009
Jump to Answer

Comments

ravikumar.sv 
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
1 person found this helpful
730428
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
Marked as Answer by 569986 · Sep 27 2020
728534
Hi,
Nice one!!!

Cheers!!!
Bhushan
ravikumar.sv 
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
1 person found this helpful
728534
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
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.
1 person found this helpful
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 4 2010
Added on Dec 2 2009
#sql
6 comments
914 views