SQL：一口气透视+分组

您无需使用PIVOT. 您的数据：

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

现在结果：

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

这只是一个并通过将不需要的值转换GROUP BY为 并选择需要的值，然后保留所需的值（因为我们需要在这里使用聚合函数）。INFO21INFO22NULLMAX

（以下部分由问题发起人添加）

在尝试理解上述答案时，我发现将下面的查询作为中间步骤进行研究很有用。

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
;

这给了，

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

这是在上面答案中使用的“分组依据”之前。

ORACLE 聚合函数（如 MAX）自动排除空值也很有用。合并行时很容易忽略它们。