데이터베이스 질문과 답변
by 행쑈 [2015.03.19 23:58:53]
--임시테이블 WITH T AS ( SELECT '홍길동' AS EMP_NM , '111111' AS EMP_NO , '2015-03-01' AS YMD FROM DUAL UNION ALL SELECT '홍길동' AS EMP_NM , '111111' AS EMP_NO , '2015-03-04' AS YMD FROM DUAL UNION ALL SELECT '홍길동' AS EMP_NM , '111111' AS EMP_NO , '2015-03-07' AS YMD FROM DUAL UNION ALL SELECT '김삿갓' AS EMP_NM , '222222' AS EMP_NO , '2015-03-01' AS YMD FROM DUAL UNION ALL SELECT '김삿갓' AS EMP_NM , '222222' AS EMP_NO , '2015-03-04' AS YMD FROM DUAL UNION ALL SELECT '김삿갓' AS EMP_NM , '222222' AS EMP_NO , '2015-03-07' AS YMD FROM DUAL UNION ALL SELECT '김삿갓' AS EMP_NM , '222222' AS EMP_NO , '2015-04-11' AS YMD FROM DUAL UNION ALL SELECT '김삿갓' AS EMP_NM , '222222' AS EMP_NO , '2015-05-20' AS YMD FROM DUAL UNION ALL SELECT '김삿갓' AS EMP_NM , '222222' AS EMP_NO , '2015-06-17' AS YMD FROM DUAL UNION ALL SELECT '김삿갓' AS EMP_NM , '222222' AS EMP_NO , '2015-08-19' AS YMD FROM DUAL UNION ALL SELECT '김삿갓' AS EMP_NM , '222222' AS EMP_NO , '2015-12-31' AS YMD FROM DUAL )
결과값을 아래와 같이 보여주고 싶습니다.
이름 ~ 날짜5 까지 컬럼 픽스이며
한사람당 5행씩 픽스입니다.
부탁드립니다.
| 이름 | 사번 | 날짜1 | 날짜2 | 날짜3 | 날짜4 | 날짜5 |
|---|---|---|---|---|---|---|
| 홍길동 | 111111 | 2015-03-01 | 2015-03-04 | 2015-03-07 | ||
| 홍길동 | 111111 | |||||
| 홍길동 | 111111 | |||||
| 홍길동 | 111111 | |||||
| 홍길동 | 111111 | |||||
| 김삿갓 | 222222 | 2015-03-01 | 2015-03-04 | 2015-03-07 | 2015-04-11 | 2015-05-20 |
| 김삿갓 | 222222 | 2015-06-17 | 2015-08-19 | 2015-12-31 | ||
| 김삿갓 | 222222 | |||||
| 김삿갓 | 222222 | |||||
| 김삿갓 | 222222 |
SELECT b.emp_nm, b.emp_no, ymd1, ymd2, ymd3, ymd4, ymd5 FROM (SELECT MAX (emp_nm) emp_nm, emp_no, MAX (DECODE (rk, 1, ymd)) ymd1, MAX (DECODE (rk, 2, ymd)) ymd2 ,MAX (DECODE (rk, 3, ymd)) ymd3, MAX (DECODE (rk, 4, ymd)) ymd4, MAX (DECODE (rk, 0, ymd)) ymd5, grp FROM (SELECT emp_nm, emp_no, ymd, MOD (rk, 5) rk, TRUNC ((rk - 1) / 5) grp FROM (SELECT emp_nm, emp_no, ymd, ROW_NUMBER () OVER (PARTITION BY emp_no ORDER BY ymd) rk FROM t)) GROUP BY emp_no, grp) a , (SELECT emp_no, emp_nm, lv FROM (SELECT DISTINCT emp_no, emp_nm FROM t) , (SELECT LEVEL - 1 lv FROM DUAL CONNECT BY LEVEL <= 5)) b WHERE b.emp_no = a.emp_no(+) AND b.lv = a.grp(+) ORDER BY 2, lv
jkson님 덕분에 잘 해결했습니다.^^
감사합니다.
저는 PIVOT 함수를 이용해 보았습니다.^^
Code{
select a.emp_no , a.emp_nm ,a.lv, b.i1,b.i2,b.i3,b.i4,b.i5
from (select *
from ( select emp_no,emp_nm from t group by emp_no,emp_nm) ,( select level lv from dual connect by level <= 5)) a ,
(
select * from(
SELECT emp_nm, emp_no, ymd, MOD (rk-1, 5)+1 rk, ceil(rk / 5) grp
FROM (SELECT emp_nm, emp_no, ymd, ROW_NUMBER () OVER (PARTITION BY emp_no ORDER BY ymd) rk
FROM t) )
PIVOT (min(ymd) for rk in(1 i1 , 2 i2 , 3 i3 , 4 i4 , 5 i5))) B
where a.emp_no = b.emp_no(+) and a.lv = b.grp(+)
order by emp_no , a.lv
}
PIVOT함수는 듣기만 했는데 이번기회에 좋은 공부 하네요..^^
쿼리back님 감사합니다.
SELECT *
FROM ( SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 5 ) a
LEFT OUTER JOIN (
SELECT emp_nm
, emp_no
, MAX ( DECODE ( MOD ( rn, 5 ), 1, ymd ) ) 날짜1
, MAX ( DECODE ( MOD ( rn, 5 ), 2, ymd ) ) 날짜2
, MAX ( DECODE ( MOD ( rn, 5 ), 3, ymd ) ) 날짜3
, MAX ( DECODE ( MOD ( rn, 5 ), 4, ymd ) ) 날짜4
, MAX ( DECODE ( MOD ( rn, 5 ), 0, ymd ) ) 날짜5
, ROW_NUMBER () OVER ( PARTITION BY emp_nm, emp_no ORDER BY CEIL ( rn / 5 ) ) rn
FROM (
SELECT t.*
, ROW_NUMBER () OVER ( PARTITION BY emp_nm, emp_no ORDER BY ymd ) rn
FROM t
)
GROUP BY emp_nm
, emp_no
, CEIL ( rn / 5 )
) b
PARTITION BY ( emp_nm, emp_no )
ON ( a.lv = b.rn )
ORDER BY emp_nm
, emp_no
, lv
) 버튼을 클릭하여 작성 하시면 됩니다.