데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

행은 늘어날 수 있지만 열은 고정 입니다.

따라서 가변 열에 대한 고정 쿼리는 불가능합니다.

동적쿼리를 이용해 아래와 같은 형태로 자료를 뽑으셔야 합니다.

WITH t AS

(

SELECT '홍길동' nm, '20100720' dt FROM dual

UNION ALL SELECT '홍길동', '20100721' FROM dual

UNION ALL SELECT '홍길동', '20100722' FROM dual

UNION ALL SELECT '홍길동', '20100723' FROM dual

UNION ALL SELECT '홍길동', '20100724' FROM dual

UNION ALL SELECT '홍길동', '20100725' FROM dual

UNION ALL SELECT '송대관', '20100721' FROM dual

UNION ALL SELECT '송대관', '20100722' FROM dual

UNION ALL SELECT '송대관', '20100723' FROM dual

UNION ALL SELECT '송대관', '20100724' FROM dual

UNION ALL SELECT '송대관', '20100725' FROM dual

UNION ALL SELECT '이춘자', '20100720' FROM dual

UNION ALL SELECT '이춘자', '20100721' FROM dual

UNION ALL SELECT '이춘자', '20100723' FROM dual

UNION ALL SELECT '이춘자', '20100724' FROM dual

UNION ALL SELECT '배동성', '20100721' FROM dual

UNION ALL SELECT '배동성', '20100722' FROM dual

UNION ALL SELECT '배동성', '20100724' FROM dual

UNION ALL SELECT '배동성', '20100725' FROM dual

UNION ALL SELECT '이승엽', '20100720' FROM dual

UNION ALL SELECT '이승엽', '20100721' FROM dual

UNION ALL SELECT '이승엽', '20100722' FROM dual

UNION ALL SELECT '이승엽', '20100723' FROM dual

UNION ALL SELECT '이승엽', '20100724' FROM dual

UNION ALL SELECT '이승엽', '20100725' FROM dual

UNION ALL SELECT '최길동', '20100721' FROM dual

UNION ALL SELECT '최길동', '20100723' FROM dual

UNION ALL SELECT '최길동', '20100724' FROM dual

UNION ALL SELECT '최길동', '20100725' FROM dual

)

SELECT nm

, NVL(MIN(DECODE(dt, '20100720', 'O')), 'X') "20100720"

, NVL(MIN(DECODE(dt, '20100721', 'O')), 'X') "20100721"

, NVL(MIN(DECODE(dt, '20100722', 'O')), 'X') "20100722"

, NVL(MIN(DECODE(dt, '20100723', 'O')), 'X') "20100723"

, NVL(MIN(DECODE(dt, '20100724', 'O')), 'X') "20100724"

, NVL(MIN(DECODE(dt, '20100725', 'O')), 'X') "20100725"

FROM t

WHERE dt BETWEEN '20100720' AND '20100725'

GROUP BY nm

;

마농(manon94)님이 2011-07-22 15:20에 작성한 댓글입니다.