데이터베이스 질문과 답변
SELECT A.INCIDENTPROGRESS,A.COUNT AS COUNT1, B.COUNT AS COUNT2, C.COUNT AS COUTN3, D.COUNT AS COUNT4 FROM
(SELECT INCIDENTPROGRESS, COUNT(DECODE(INCIDENTCODE,1,1,NULL))AS COUNT FROM TB_OP_INCIDENT GROUP BY INCIDENTPROGRESS) A,
(SELECT INCIDENTPROGRESS, COUNT(DECODE(INCIDENTCODE,2,1,NULL))AS COUNT FROM TB_OP_INCIDENT GROUP BY INCIDENTPROGRESS) B,
(SELECT INCIDENTPROGRESS, COUNT(DECODE(INCIDENTCODE,3,1,NULL))AS COUNT FROM TB_OP_INCIDENT GROUP BY INCIDENTPROGRESS) C,
(SELECT INCIDENTPROGRESS, COUNT(DECODE(INCIDENTCODE,4,1,NULL))AS COUNT FROM TB_OP_INCIDENT GROUP BY INCIDENTPROGRESS) D
WHERE
A.INCIDENTPROGRESS = B.INCIDENTPROGRESS AND
A.INCIDENTPROGRESS = C.INCIDENTPROGRESS AND
A.INCIDENTPROGRESS = D.INCIDENTPROGRESS
ORDER BY INCIDENTPROGRESS;
현재 이렇게 짜여져 있는 쿼리를 PIVOT을 사용해서 변경할려고 합니다.
그런데 PIVOT을 사용해보지 않아 어렵네요
선배님들의 조언 부탁드립니다.
by 우리집아찌
[2013.11.12 10:06:04]
1 2 3 4 5 6 7 8 | SELECT INCIDENTPROGRESS , COUNT(DECODE(INCIDENTCODE,1,1,NULL))AS COUNT1 , COUNT(DECODE(INCIDENTCODE,2,1,NULL))AS COUNT2 , COUNT(DECODE(INCIDENTCODE,3,1,NULL))AS COUNT3 , COUNT(DECODE(INCIDENTCODE,4,1,NULL))AS COUNT4 FROM TB_OP_INCIDENT GROUP BY INCIDENTPROGRESS ORDER BY INCIDENTPROGRESS; |
by IT노동자
[2013.11.12 10:24:21]
아찌님 감사합니다...ㅜㅜ
by 마농
[2013.11.12 10:12:05]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- 새것을 배우는 것도 좋지만...SELECT * FROM tb_op_incident PIVOT (COUNT(*) FOR incidentcode IN (1, 2, 3, 4)) ORDER BY incidentprogress;-- 옛것을 잘 사용하는 방법을 먼저 익히세요...SELECT incidentprogress , COUNT(DECODE(incidentcode, 1, 1)) cnt1 , COUNT(DECODE(incidentcode, 2, 1)) cnt2 , COUNT(DECODE(incidentcode, 3, 1)) cnt3 , COUNT(DECODE(incidentcode, 4, 1)) cnt4 FROM tb_op_incident GROUP BY incidentprogress ORDER BY incidentprogress; |
by IT노동자
[2013.11.12 10:24:05]
마농님 말씀 새겨듣겠습니다...ㅜㅜ
쿼리를 많이 짜봐야되는데 아직 미숙하네요 ㅜㅜ
감사합니다
쿼리를 많이 짜봐야되는데 아직 미숙하네요 ㅜㅜ
감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.