데이터베이스 질문과 답변
| 년도 | 날짜1 | 날짜2 | 날짜3 |
| 2000 | 20091023 | ||
| 2000 | 20091023 | ||
| 2000 | 20091023 | ||
| 2000 | 20091024 | ||
| 2000 | 20091023 | ||
| 2000 | 20091026 | ||
| 2000 | 20091026 | ||
| 2000 | 20091026 | ||
| 2000 | 20091026 | ||
| 위와 같은 테이블을 아래와 같이 바꿀려면 어떻게 해야되죠 | |||
| 년도 | 날짜1 | 날짜2 | 날짜3 |
| 2000 | 20091023 | 20091024 | 20091026 |
| 2000 | 20091023 | 20091023 | 20091026 |
| 2000 | 20091023 | 20091026 | |
| 2000 | 20091026 | ||
필드가 3개 일때는 잘 안됩니다.
by 서성우
[2009.10.26 11:48:04]
WITH test AS
(
SELECT '2000' yer , '20091023' dat1 , NULL dat2 , NULL dat3 FROM dual UNION ALL
SELECT '2000' , '20091023' , NULL ,NULL FROM dual UNION ALL
SELECT '2000' , '20091023' , NULL ,NULL FROM dual UNION ALL
SELECT '2000' , null , '20091024' ,NULL FROM dual UNION ALL
SELECT '2000' , null , '20091024' ,NULL FROM dual UNION ALL
SELECT '2000' , null , NULL ,'20091026' FROM dual UNION ALL
SELECT '2000' , null , NULL ,'20091026' FROM dual UNION ALL
SELECT '2000' , NULL , NULL ,'20091026' FROM dual UNION ALL
SELECT '2000' , NULL , NULL ,'20091026' FROM dual
)
SELECT yer ,
Min(dat1) dat1,
Min(dat2) dat2,
Min(dat3) dat3
FROM
(SELECT yer ,
dat1 ,
dat2 ,
dat3 ,
Row_Number() over(PARTITION BY yer,dat1,dat2,dat3 ORDER BY dat1,dat2,dat3) rn
FROM test)
GROUP BY yer,rn
by 마농
[2009.10.26 12:58:53]
SELECT yer
, MIN(dat1) dat1
, MIN(dat2) dat2
, MIN(dat3) dat3
FROM
(
SELECT yer, dat1, dat2, dat3
, ROW_NUMBER() OVER(PARTITION BY yer, NVL2(dat1,1,NVL2(dat2,2,3)) ORDER BY dat1, dat2, dat3) rn
FROM test
)
GROUP BY yer, rn
;
, MIN(dat1) dat1
, MIN(dat2) dat2
, MIN(dat3) dat3
FROM
(
SELECT yer, dat1, dat2, dat3
, ROW_NUMBER() OVER(PARTITION BY yer, NVL2(dat1,1,NVL2(dat2,2,3)) ORDER BY dat1, dat2, dat3) rn
FROM test
)
GROUP BY yer, rn
;
by 질문자
[2009.10.26 13:28:42]
두분 모두에게 감사드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.