oracle 编程怎么更新一行,从多行创建一行oracle

您可以在Oracle 11g中使用旋转功能

WITH TABLE1(ID, VER, DDATE) AS (

select 120 , 1 , '01/03/14' from dual union all

select 120 , 2 , '02/03/14' FROM DUAL UNION ALL

select 120 , 3 , '04/03/14' from dual union all

select 110 , 1 , '01/03/14' FROM DUAL UNION ALL

select 130 , 1 , '02/03/14' FROM DUAL UNION ALL

SELECT 130 , 2 , '11/03/14' FROM DUAL)

------------

---- End of Data

------------

SELECT *

FROM TABLE1

PIVOT (MIN(VER) AS VER, MIN(DDATE) FOR VER IN (1 as DATE1, 2 as DATE2, 3 as DATE3, 4 as DATE4, 5 as DATE5));

在之前版本执行它Oracle，您可以使用情况和MIN

WITH TABLE1(ID, VER, DDATE) AS (

select 120 , 1 , '01/03/14' from dual union all

select 120 , 2 , '02/03/14' FROM DUAL UNION ALL

select 120 , 3 , '04/03/14' from dual union all

select 110 , 1 , '01/03/14' FROM DUAL UNION ALL

select 130 , 1 , '02/03/14' FROM DUAL UNION ALL

SELECT 130 , 2 , '11/03/14' FROM DUAL)

------------

---- End of Data

------------

SELECT ID,

MIN(CASE WHEN VER = 1 THEN VER ELSE NULL END) AS VER1,

MIN(CASE WHEN VER = 1 THEN DDATE ELSE NULL END) AS DDATE1,

MIN(CASE WHEN VER = 2 THEN VER ELSE NULL END) AS VER2,

MIN(CASE WHEN VER = 2 THEN DDATE ELSE NULL END) as DDATE2,

MIN(CASE WHEN VER = 3 THEN VER ELSE NULL END) AS VER3,

MIN(CASE WHEN VER = 3 THEN DDATE ELSE NULL END) as DDATE3,

MIN(CASE WHEN VER = 4 THEN VER ELSE NULL END) AS VER5,

MIN(CASE WHEN VER = 4 THEN DDATE ELSE NULL END) as DDATE4,

MIN(CASE WHEN VER = 5 THEN VER ELSE NULL END) AS VER6,

min(case when ver = 5 then DDATE else null end) as DDATE5

FROM TABLE1

group by id;

输出在这两种情况下是

| ID | VER1 | DDATE1 | VER2 | DDATE2 | VER3 | DDATE3 | VER5 | DDATE4 | VER6 | DDATE5 |

|-----|------|----------|--------|----------|--------|----------|--------|--------|--------|--------|

| 120 | 1 | 01/03/14 | 2 | 02/03/14 | 3 | 04/03/14 | (null) | (null) | (null) | (null) |

| 110 | 1 | 01/03/14 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |

| 130 | 1 | 02/03/14 | 2 | 11/03/14 | (null) | (null) | (null) | (null) | (null) | (null) |

对于你的表，你可以使用

SELECT *

FROM

PIVOT (MIN(VER) AS VER, MIN(DDATE) FOR VER IN (1 as DATE1, 2 as DATE2, 3 as DATE3, 4 as DATE4, 5 as DATE5));

或

SELECT ID,

MIN(CASE WHEN VER = 1 THEN VER ELSE NULL END) AS VER1,

MIN(CASE WHEN VER = 1 THEN DDATE ELSE NULL END) AS DDATE1,

MIN(CASE WHEN VER = 2 THEN VER ELSE NULL END) AS VER2,

MIN(CASE WHEN VER = 2 THEN DDATE ELSE NULL END) as DDATE2,

MIN(CASE WHEN VER = 3 THEN VER ELSE NULL END) AS VER3,

MIN(CASE WHEN VER = 3 THEN DDATE ELSE NULL END) as DDATE3,

MIN(CASE WHEN VER = 4 THEN VER ELSE NULL END) AS VER5,

MIN(CASE WHEN VER = 4 THEN DDATE ELSE NULL END) as DDATE4,

MIN(CASE WHEN VER = 5 THEN VER ELSE NULL END) AS VER6,

min(case when ver = 5 then DDATE else null end) as DDATE5

FROM

group by id;