Forum Stats

  • 3,853,190 Users
  • 2,264,189 Discussions
  • 7,905,282 Comments

Discussions

unpivot query

proora
proora Member Posts: 204
edited Mar 26, 2015 7:00AM in SQL & PL/SQL

Dears ,

I have like the below table

CodeCountCode CountCodeCountCodeCount
A114A1511

A10

15

D0315D1115D0122

D02

75

J012J507

J15

11

Q22

24

Q017
A7114

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 CodeCode
A10A11
A10A15
A10A10
D02D03
D02D11
D02D01
D02D02
J15J01
J15J50
J15J15
Q22Q22
Q22Q01
A08A71
A08A08

regards,

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,326 Red Diamond
    edited Mar 26, 2015 6:47AM

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

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Mar 26, 2015 6:59AM

    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
    BluShadow Member, Moderator Posts: 42,326 Red Diamond
    edited Mar 26, 2015 6:59AM

    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
    Stew Ashton Member Posts: 2,911 Bronze Crown
    edited Mar 26, 2015 7:00AM

    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_CODECODE
    A10A11
    A10A15
    A10A10
    A08A71
    A08A08
    D02D11
    D02D03
    D02D01
    D02D02
    J15J01
    J15J50
    J15J15
    Q22Q01
    Q22Q22
This discussion has been closed.