原始记录如下JObNO DATE1 DATE2
G00003 2011-07-04 14:24:17.000 2011-07-04 14:32:41.000
现在想分成2条记录 DATE1 和 DATE2 都叫OPDATE,DATE1不为空变成IN ,DATE2不为空变成OUT JObNO DATE1 OPDATE
G00003 IN 2011-07-04 14:24:17.000
G00003 OUT 2011-07-04 14:32:41.000如何写SQL
G00003 2011-07-04 14:24:17.000 2011-07-04 14:32:41.000
现在想分成2条记录 DATE1 和 DATE2 都叫OPDATE,DATE1不为空变成IN ,DATE2不为空变成OUT JObNO DATE1 OPDATE
G00003 IN 2011-07-04 14:24:17.000
G00003 OUT 2011-07-04 14:32:41.000如何写SQL
from tb
union all
select JObNO null DATE1, OPDATE2
from tb
select JObNO,'in' as [DATE1], DATE1 as OPDATE from tb where DATE1 is not null
union all
select JObNO,'out' as [DATE1],DATE2 as OPDATE from tb where DATE2 is not null