데이터베이스 질문과 답변
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]
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]
-- 새것을 배우는 것도 좋지만...
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]
마농님 말씀 새겨듣겠습니다...ㅜㅜ
쿼리를 많이 짜봐야되는데 아직 미숙하네요 ㅜㅜ
감사합니다
쿼리를 많이 짜봐야되는데 아직 미숙하네요 ㅜㅜ
감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.