SQL: Pivot + Group by in one go I wish to pivot INFO2 into 2 columns.
INFO3 data should adjusted accordingly after the PIVOT.

The challenge is I also have a date column, which I want to take the MAX of.
The MAX(Date) should also be grouped by (lot_no, mcid).

Is there a way of doing this in one fell swoop?
Without resorting to temporary tables?
You can easily do that without using PIVOT. Your data:

with data as
(
  select 1 as lot_no, 1 as mcid, 1 as info2, 111 as info3, date'2015-01-01' as dat from dual union all
  select 1, 1, 2, 222, date'2015-01-02' from dual union all
  select 2, 2, 1, 333, date'2015-01-03' from dual union all
  select 2, 2, 2, 444, date'2015-01-04' from dual  
)
select * from data;

    LOT_NO       MCID      INFO2      INFO3 DAT
---------- ---------- ---------- ---------- -------------------
         1          1          1        111 2015-01-01 00:00:00
         1          1          2        222 2015-01-02 00:00:00
         2          2          1        333 2015-01-03 00:00:00
         2          2          2        444 2015-01-04 00:00:00


Now the result:

with data as
(
  select 1 as lot_no, 1 as mcid, 1 as info2, 111 as info3, date'2015-01-01' as dat from dual union all
  select 1, 1, 2, 222, date'2015-01-02' from dual union all
  select 2, 2, 1, 333, date'2015-01-03' from dual union all
  select 2, 2, 2, 444, date'2015-01-04' from dual  
)
select
  lot_no,
  mcid,
  max(decode(info2, 1, info3, null)) as info21,
  max(decode(info2, 2, info3, null)) as info22,
  max(dat)
from
  data
group by
  lot_no,
  mcid
;

    LOT_NO       MCID     INFO21     INFO22 MAX(DAT)
---------- ---------- ---------- ---------- -------------------
         1          1        111        222 2015-01-02 00:00:00
         2          2        333        444 2015-01-04 00:00:00


This is simply a GROUP BY and picking the need value for INFO21 and INFO22 by transforming unneeded values into NULL, then keeping the needed value with MAX (because we need to use an aggregate function here).



(Section below added by question originator)

While trying to understand the above answer, I found it useful to study the query below as an intermediate step. 

with data as
(
  select 1 as lot_no, 1 as mcid, 1 as info2, 111 as info3, date'2015-01-01' as dat from dual union all
  select 1, 1 , 2, 222, date'2015-01-02' from dual union all
  select 2, 2 , 1, 333, date'2015-01-03' from dual union all
  select 2, 2 , 2, 444, date'2015-01-04' from dual 
)
select
  lot_no,
  mcid,
  decode(info2, 1, info3, null) as info21,
  decode(info2, 2, info3, null) as info22,
  --max(dat)
  dat
from
  data
;


This gives, 

LOT_NO  MCID    INFO21  INFO22  DAT
1   1   111     1 Jan 15
1   1       222 2 Jan 15
2   2   333     3 Jan 15
2   2       444 4 Jan 15




This is before the "group-by" used in the answer above. 

It's also useful that null values are automatically excluded, by ORACLE aggregate functions like MAX. Makes it easy to ignore them when merging rows.