unpivot query

proora

Dears ,

I have like the below table

Code	Count	Code	Count	Code	Count	Code	Count
A11	4	A15	11	A10	15
D03	15	D11	15	D01	22	D02	75
J01	2	J50	7	J15	11
Q22	24	Q01	7
A71	14	A08	51

i tried to unpivot by still not coming with me

i want this out put , which most counted code in column and the other code in same row in another column

Most Counted Code	Code
A10	A11
A10	A15
A10	A10
D02	D03
D02	D11
D02	D01
D02	D02
J15	J01
J15	J50
J15	J15
Q22	Q22
Q22	Q01
A08	A71
A08	A08

regards,

BluShadow

And what if there is more than one count with the same count?

Frank Kulash

Hi,

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

Explain, using specific examples, how you get those results from that data.

Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

See the forum FAQ: 

proora wrote:
... i tried to unpivot by still not coming with me  ...

Post your code.  It's very hard to tell what you're doing wrong without knowing what you're doing.

BluShadow

Based on your current requirements, something like...

SQL> ed
Wrote file afiedt.buf
  1  with t(code1, count1, code2, count2, code3, count3, code4, count4) as (
  2         select 'A11', 4, 'A15', 11, 'A10', 15, null, null from dual union all
  3         select 'D03', 15, 'D11', 15, 'D01', 22, 'D02', 75 from dual union all
  4         select 'J01', 2, 'J50', 7, 'J15', 11, null, null from dual union all
  5         select 'Q22', 24, 'Q01', 7, null, null, null, null from dual union all
  6         select 'A71', 14, 'A08', 51, null, null, null, null from dual
  7        )
  8  --
  9  -- end of test data
 10  --
 11      ,u as (
 12         select code1, count1, code2, count2, code3, count3, code4, count4
 13               ,row_number() over (order by 1) as unique_key
 14         from t
 15        )
 16      ,unpiv as (
 17         select decode(rn,1,code1,2,code2,3,code3,4,code4) as code
 18               ,decode(rn,1,count1,2,count2,3,count3,4,count4) as cnt
 19               ,unique_key
 20         from   u
 21                cross join (select rownum rn from dual connect by rownum <= 4)
 22        )
 23      ,mx as (select max(code) keep (dense_rank first order by cnt desc nulls last) as code
 24                    ,unique_key
 25              from   unpiv
 26              group by unique_key
 27             )
 28  select mx.code
 29        ,unpiv.code
 30  from   mx
 31         join unpiv on (unpiv.unique_key = mx.unique_key)
 32  where  unpiv.code is not null
 33* order by 1,2
SQL> /
COD COD
--- ---
A08 A08
A08 A71
A10 A10
A10 A11
A10 A15
D02 D01
D02 D02
D02 D03
D02 D11
J15 J01
J15 J15
J15 J50
Q22 Q01
Q22 Q22


14 rows selected.

Stew Ashton

This should help you understand how to do the UNPIVOT:

drop table t purge;
create table t (Code1,Count1,Code2,Count2,Code3,Count3,Code4,Count4) as select
'A11',4,'A15',11,'A10',15, null, cast(null as number) from dual union all select
'D03',15,'D11',15,'D01',22,'D02',75 from dual union all select
'J01',2,'J50',7,'J15',11,null,null from dual union all select
'Q22',24,'Q01',7,null,null,null,null from dual union all select
'A71',14,'A08',51,null,null,null,null from dual;

select
first_value(code) over(partition by rn order by count desc, code) max_code,
code
from (
  select t.*, row_number() over(order by code1) rn
  from t
)
unpivot((code,count) for col in((code1,count1),(code2,count2),(code3,count3),(code4,count4)))
order by rn, count;

MAX_CODE	CODE
A10	A11
A10	A15
A10	A10
A08	A71
A08	A08
D02	D11
D02	D03
D02	D01
D02	D02
J15	J01
J15	J50
J15	J15
Q22	Q01
Q22	Q22