본문 바로가기

Personal_Area/Personal PDS(Locked)

pivot 스타일로 디비데이터 가져오기


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

'Personal_Area > Personal PDS(Locked)' 카테고리의 다른 글

[LINK] TABLE CSS 동적 생성 사이트  (0) 2013.08.22
플렉스터 M5Pro 구매후기  (0) 2013.07.12
Test3  (0) 2013.06.27
페이스북 연동 테스트2  (0) 2013.06.27
EDITPLUS_SQL_GRAMMER_REFERENCE  (0) 2013.04.15
DDL2_iBATIS  (0) 2011.08.10
CVSNT 설치파일 및 설치매뉴얼  (0) 2011.08.10
안드로이드 노트정리 v0805 AM  (0) 2010.08.05
프로젝트 관련 공지  (0) 2010.08.03
윈도우 7 체험지수  (0) 2010.05.23