Personal_Area/Personal PDS(Locked)
pivot 스타일로 디비데이터 가져오기
DionysosJH™
2011. 10. 7. 15:00
SELECT
TEMP.OWNER AS OWNER
,TEMP.HR AS HR
,TEMP.ADMIN AS ADMIN
,TEMP.EMPLOYEE AS EMPLOYEE
,'' AS AVGR --평균을 구해주기 위한 공갈컬럼
FROM
(
SELECT
(SELECT ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='1'
AND IM.QSTION_CLASS='A'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='1'
AND IM.QSTION_CLASS='A'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "OWNER"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='2'
AND IM.QSTION_CLASS='A'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='2'
AND IM.QSTION_CLASS='A'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "HR"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='3'
AND IM.QSTION_CLASS='A'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='3'
AND IM.QSTION_CLASS='A'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "ADMIN"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='4'
AND IM.QSTION_CLASS='A'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='4'
AND IM.QSTION_CLASS='A'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "EMPLOYEE"
FROM DUAL
UNION ALL
SELECT
(SELECT ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='1'
AND IM.QSTION_CLASS='B'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='1'
AND IM.QSTION_CLASS='B'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "OWNER"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='2'
AND IM.QSTION_CLASS='B'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='2'
AND IM.QSTION_CLASS='B'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "HR"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='3'
AND IM.QSTION_CLASS='B'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='3'
AND IM.QSTION_CLASS='B'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "ADMIN"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='4'
AND IM.QSTION_CLASS='B'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='4'
AND IM.QSTION_CLASS='B'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "EMPLOYEE"
FROM DUAL
UNION ALL
SELECT
(SELECT ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='1'
AND IM.QSTION_CLASS='C'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='1'
AND IM.QSTION_CLASS='C'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "OWNER"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='2'
AND IM.QSTION_CLASS='C'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='2'
AND IM.QSTION_CLASS='C'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "HR"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='3'
AND IM.QSTION_CLASS='C'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='3'
AND IM.QSTION_CLASS='B'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "ADMIN"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='4'
AND IM.QSTION_CLASS='C'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='4'
AND IM.QSTION_CLASS='C'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "EMPLOYEE"
FROM DUAL
UNION ALL
SELECT
(SELECT ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='1'
AND IM.QSTION_CLASS='D'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='1'
AND IM.QSTION_CLASS='D'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "OWNER"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='2'
AND IM.QSTION_CLASS='D'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='2'
AND IM.QSTION_CLASS='D'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "HR"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='3'
AND IM.QSTION_CLASS='D'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='3'
AND IM.QSTION_CLASS='D'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "ADMIN"
,
(SELECT
ROUND(
(SUM(RD.REPLY_DETAIL)
/(SELECT COUNT(RD.ITEM_ID)
FROM HR_ID_DIAGNS_REPLY_DETAIL RD , HR_ITEM_MST IM
WHERE RD.DIAGNSRPT_ID='RPT01'
AND RD.DIAGNSRPT_ID=IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO='4'
AND IM.QSTION_CLASS='D'
)
) ,2) AS REPLY_DETAIL
FROM HRKPD.HR_ID_DIAGNS_REPLY_DETAIL RD , HRKPD.HR_ID_DIAGNS_TARGET_LIST TL, HRKPD.HR_ITEM_MST IM --10
WHERE RD.ACCEPT_SEQ_NO=TL.ACCEPT_SEQ_NO
AND RD.DIAGNSRPT_ID =IM.DIAGNSRPT_ID
AND RD.ITEM_ID=IM.ITEM_ID
AND RD.DIAGNSRPT_ID='RPT01'
AND RD.ACCEPT_SEQ_NO='20012753'
AND RD.SEQ_NO=TL.SEQ_NO
AND TL.TITLE='4'
AND IM.QSTION_CLASS='D'
GROUP BY RD.SEQ_NO, RD.ACCEPT_SEQ_NO,RD.DIAGNSRPT_ID,TL.TITLE
) AS "EMPLOYEE"
FROM DUAL
) TEMP