Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE ABC
(
     key NUMBER(5), 
     orders NUMBER(5), 
     cost NUMBER(5), 
     dat DATE
);
begin
insert into ABC (key, orders, cost, dat) values (1, 3, 5, to_date('10-11- 
2017', 'mm-dd-yyyy'));
insert into ABC (key, orders, cost,dat) values (1, 5, 2, to_date('02-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 6, 1, to_date('03-10- 
2017', 'mm-dd-yyyy'));
insert into ABC (key, orders, cost,dat) values (1, 7, 2, to_date('05-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 8, 3, to_date('07-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 3, 4, to_date('08-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 6, to_date('02-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 9, to_date('01-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 2 ,5, to_date('03-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 2, to_date('05-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 1, 1, to_date('06-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 4, 12, to_date('10-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 3, 9, to_date('01-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 2 ,5, to_date('05-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 3, 2, to_date('06-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 1, 1, to_date('07-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 4, 12, to_date('11-10- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost, dat) values (1, 3, 5, to_date('10-01- 
2017', 'mm-dd-yyyy'));
insert into ABC (key, orders, cost,dat) values (1, 5, 2, to_date('02-17- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 6, 1, to_date('03-18- 
2017', 'mm-dd-yyyy'));
insert into ABC (key, orders, cost,dat) values (1, 7, 2, to_date('05-14- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 8, 3, to_date('07-13- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (1, 3, 4, to_date('08-12- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 6, to_date('02-11- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 9, to_date('01-15- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 2 ,5, to_date('03-14- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 3, 2, to_date('05-18- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (2, 1, 1, to_date('06-19- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 4, 12, to_date('10-11- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 3, 9, to_date('01-12- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 2 ,5, to_date('05-16- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 3, 2, to_date('06-17- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 1, 1, to_date('07-12- 
2017', 'mm-dd-yyyy')); 
insert into ABC (key, orders, cost,dat) values (3, 4, 12, to_date('12-21- 
2017', 'mm-dd-yyyy'));
end ;
/
1 rows affected

  select *
  from (
    select key, dat, to_char( dat, 'Q' ) quarter 
    , max( cost ) over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) maxcost_
    , max( dat ) over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) maxdat_
    , row_number()  over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) rownum_
    from abc
  )
  where rownum_ = 1 ;


KEY
DAT
QUARTER
MAXCOST_
MAXDAT_
ROWNUM_


1
17-FEB-17
1
2
17-FEB-17
1

1
14-MAY-17
2
2
14-MAY-17
1

1
12-AUG-17
3
4
12-AUG-17
1

1
01-OCT-17
4
5
11-OCT-17
1

2
10-JAN-17
1
9
15-JAN-17
1

2
10-MAY-17
2
2
18-MAY-17
1

3
10-JAN-17
1
9
12-JAN-17
1

3
10-MAY-17
2
5
16-MAY-17
1

3
10-JUL-17
3
1
12-JUL-17
1

3
10-NOV-17
4
12
21-DEC-17
1


select unique key
,  nth_value( maxcost_, 1 ) from first over ( partition by key ) q1max
,  nth_value( maxdat_, 1 ) from first over ( partition by key ) q1date
,  nth_value( maxcost_, 2 ) from first over ( partition by key ) q2max
,  nth_value( maxdat_, 2 ) from first over ( partition by key ) q2date
,  nth_value( maxcost_, 3 ) from first over ( partition by key ) q3max
,  nth_value( maxdat_, 3 ) from first over ( partition by key ) q3date
,  nth_value( maxcost_, 4 ) from first over ( partition by key ) q4cost
,  nth_value( maxdat_, 4 ) from first over ( partition by key ) q4max
from (
  select *
  from (
    select key, dat, to_char( dat, 'Q' ) quarter 
    , max( cost ) over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) maxcost_
    , max( dat ) over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) maxdat_
    , row_number()  over ( partition by key, to_char( dat, 'Q' ) order by cost desc ) rownum_
    from abc
  )
  where rownum_ = 1  
)
order by key
;


KEY
Q1MAX
Q1DATE
Q2MAX
Q2DATE
Q3MAX
Q3DATE
Q4COST
Q4MAX


1
2
17-FEB-17
2
14-MAY-17
4
12-AUG-17
5
11-OCT-17

2
9
15-JAN-17
2
18-MAY-17
null
null
null
null

3
9
12-JAN-17
5
16-MAY-17
1
12-JUL-17
12
21-DEC-17


-- -----------------------------------------------------------------------------
--    DENSIFY: get 12 values per key (one for each month) 
-- -----------------------------------------------------------------------------
select *
from (
  select *
  from (
    select key, dat, to_number( to_char( dat, 'MM' ) ) as month_ 
    , max( cost ) over ( partition by key, to_char( dat, 'MM' ) order by cost desc ) maxcost_
    , max( dat ) over ( partition by key, to_char( dat, 'MM' ) order by cost desc ) maxdat_
    , row_number()  over ( partition by key, to_char( dat, 'MM' ) order by cost desc ) rownum_
    from abc
  ) 
  where rownum_ = 1
) A partition by ( key ) right join ( 
  select level as month_ from dual connect by level <= 12
) M on A.month_ = M.month_ 
;


KEY
DAT
MONTH_
MAXCOST_
MAXDAT_
ROWNUM_
MONTH_


1
null
null
null
null
null
1

1
17-FEB-17
2
2
17-FEB-17
1
2

1
10-MAR-17
3
1
18-MAR-17
1
3

1
null
null
null
null
null
4

1
14-MAY-17
5
2
14-MAY-17
1
5

1
null
null
null
null
null
6

1
10-JUL-17
7
3
13-JUL-17
1
7

1
12-AUG-17
8
4
12-AUG-17
1
8

1
null
null
null
null
null
9

1
01-OCT-17
10
5
11-OCT-17
1
10

1
null
null
null
null
null
11

1
null
null
null
null
null
12

2
10-JAN-17
1
9
15-JAN-17
1
1

2
10-FEB-17
2
6
11-FEB-17
1
2

2
14-MAR-17
3
5
14-MAR-17
1
3

2
null
null
null
null
null
4

2
10-MAY-17
5
2
18-MAY-17
1
5

2
10-JUN-17
6
1
19-JUN-17
1
6

2
null
null
null
null
null
7

2
null
null
null
null
null
8

2
null
null
null
null
null
9

2
null
null
null
null
null
10

2
null
null
null
null
null
11

2
null
null
null
null
null
12

3
10-JAN-17
1
9
12-JAN-17
1
1

3
null
null
null
null
null
2

3
null
null
null
null
null
3

3
null
null
null
null
null
4

3
10-MAY-17
5
5
16-MAY-17
1
5

3
10-JUN-17
6
2
17-JUN-17
1
6

3
10-JUL-17
7
1
12-JUL-17
1
7

3
null
null
null
null
null
8

3
null
null
null
null
null
9

3
10-OCT-17
10
12
11-OCT-17
1
10

3
10-NOV-17
11
12
10-NOV-17
1
11

3
21-DEC-17
12
12
21-DEC-17
1
12


select unique key
,  nth_value( maxcost_, 1 ) from first over ( partition by key ) m1max
,  nth_value( maxdat_, 1 ) from first over ( partition by key ) m1date
,  nth_value( maxcost_, 2 ) from first over ( partition by key ) m2max
,  nth_value( maxdat_, 2 ) from first over ( partition by key ) m2date
,  nth_value( maxcost_, 3 ) from first over ( partition by key ) m3max
,  nth_value( maxdat_, 3 ) from first over ( partition by key ) m3date
,  nth_value( maxcost_, 4 ) from first over ( partition by key ) m4max
,  nth_value( maxdat_, 4 ) from first over ( partition by key ) m4date
,  nth_value( maxcost_, 5 ) from first over ( partition by key ) m5max
,  nth_value( maxdat_, 5 ) from first over ( partition by key ) m5date
,  nth_value( maxcost_, 6 ) from first over ( partition by key ) m6max
,  nth_value( maxdat_, 6 ) from first over ( partition by key ) m6date
,  nth_value( maxcost_, 7 ) from first over ( partition by key ) m7max
,  nth_value( maxdat_, 7 ) from first over ( partition by key ) m7date
,  nth_value( maxcost_, 8 ) from first over ( partition by key ) m8max
,  nth_value( maxdat_, 8 ) from first over ( partition by key ) m8date
,  nth_value( maxcost_, 9 ) from first over ( partition by key ) m9max
,  nth_value( maxdat_, 9 ) from first over ( partition by key ) m9date
,  nth_value( maxcost_, 10 ) from first over ( partition by key ) m10max
,  nth_value( maxdat_, 10 ) from first over ( partition by key ) m10date
,  nth_value( maxcost_, 11 ) from first over ( partition by key ) m11max
,  nth_value( maxdat_, 11 ) from first over ( partition by key ) m11date
,  nth_value( maxcost_, 12 ) from first over ( partition by key ) m12max
,  nth_value( maxdat_, 12 ) from first over ( partition by key ) m12date
from (
  select *
  from (
    select *
    from (
      select key, dat, to_number( to_char( dat, 'MM' ) ) as month_ 
      , max( cost ) over ( partition by key, to_char( dat, 'MM' ) order by cost desc ) maxcost_
      , max( dat ) over ( partition by key, to_char( dat, 'MM' ) order by cost desc ) maxdat_
      , row_number()  over ( partition by key, to_char( dat, 'MM' ) order by cost desc ) rownum_
      from abc
    ) 
    where rownum_ = 1
  ) A partition by ( key ) right join ( 
    select level as month_ from dual connect by level <= 12
  ) M on A.month_ = M.month_ 
)
;


KEY
M1MAX
M1DATE
M2MAX
M2DATE
M3MAX
M3DATE
M4MAX
M4DATE
M5MAX
M5DATE
M6MAX
M6DATE
M7MAX
M7DATE
M8MAX
M8DATE
M9MAX
M9DATE
M10MAX
M10DATE
M11MAX
M11DATE
M12MAX
M12DATE


1
null
null
2
17-FEB-17
1
18-MAR-17
null
null
2
14-MAY-17
null
null
3
13-JUL-17
4
12-AUG-17
null
null
5
11-OCT-17
null
null
null
null

2
9
15-JAN-17
6
11-FEB-17
5
14-MAR-17
null
null
2
18-MAY-17
1
19-JUN-17
null
null
null
null
null
null
null
null
null
null
null
null

3
9
12-JAN-17
null
null
null
null
null
null
5
16-MAY-17
2
17-JUN-17
1
12-JUL-17
null
null
null
null
12
11-OCT-17
12
10-NOV-17
12
21-DEC-17

KEY	DAT	MONTH_	MAXCOST_	MAXDAT_	ROWNUM_	MONTH_
1	null	null	null	null	null	1
1	17-FEB-17	2	2	17-FEB-17	1	2
1	10-MAR-17	3	1	18-MAR-17	1	3
1	null	null	null	null	null	4
1	14-MAY-17	5	2	14-MAY-17	1	5
1	null	null	null	null	null	6
1	10-JUL-17	7	3	13-JUL-17	1	7
1	12-AUG-17	8	4	12-AUG-17	1	8
1	null	null	null	null	null	9
1	01-OCT-17	10	5	11-OCT-17	1	10
1	null	null	null	null	null	11
1	null	null	null	null	null	12
2	10-JAN-17	1	9	15-JAN-17	1	1
2	10-FEB-17	2	6	11-FEB-17	1	2
2	14-MAR-17	3	5	14-MAR-17	1	3
2	null	null	null	null	null	4
2	10-MAY-17	5	2	18-MAY-17	1	5
2	10-JUN-17	6	1	19-JUN-17	1	6
2	null	null	null	null	null	7
2	null	null	null	null	null	8
2	null	null	null	null	null	9
2	null	null	null	null	null	10
2	null	null	null	null	null	11
2	null	null	null	null	null	12
3	10-JAN-17	1	9	12-JAN-17	1	1
3	null	null	null	null	null	2
3	null	null	null	null	null	3
3	null	null	null	null	null	4
3	10-MAY-17	5	5	16-MAY-17	1	5
3	10-JUN-17	6	2	17-JUN-17	1	6
3	10-JUL-17	7	1	12-JUL-17	1	7
3	null	null	null	null	null	8
3	null	null	null	null	null	9
3	10-OCT-17	10	12	11-OCT-17	1	10
3	10-NOV-17	11	12	10-NOV-17	1	11
3	21-DEC-17	12	12	21-DEC-17	1	12