oracle 행을 열로 바꾸기, 가장 큰값, 평균 큰값 출력하기

테이블에 존재하는 과목, 점수, 이름 항목의 oracle 행을 열로 바꾸고

과목 점수가 가장 큰 사람과 평균 과목 점수가 가장 큰사람의 리스트를 출력하겠다.

1. 테이블 생성

CREATE TABLE SCOTT.TEST_TABLE
(
  NM     VARCHAR2(100 BYTE),
  OBJ    VARCHAR2(100 BYTE),
  SCORE  NUMBER(10)
)

2.1 함수 사용 하지 않고 출력하기

SELECT 
    
    (SELECT 
         NM 
       FROM 
       TEST_TABLE 
      WHERE 
      SCORE=(SELECT MAX(SCORE) 
       FROM TEST_TABLE
      WHERE OBJ='JAVA') 
        AND OBJ='JAVA') AS JAVA_TOP
    ,
    (SELECT 
         NM 
       FROM 
       TEST_TABLE 
      WHERE 
      SCORE=(SELECT MAX(SCORE) 
       FROM TEST_TABLE
      WHERE OBJ='JSP') 
        AND OBJ='JSP') AS JSP_TOP 
    ,
    (SELECT 
         NM 
       FROM 
       TEST_TABLE 
      WHERE 
      SCORE=(SELECT MAX(SCORE) 
       FROM TEST_TABLE
      WHERE OBJ='ORACLE') 
        AND OBJ='ORACLE') AS ORACLE_TOP 
    ,
    (SELECT 
         NM 
       FROM 
       TEST_TABLE 
      WHERE 
      SCORE=(SELECT MAX(SCORE) 
       FROM TEST_TABLE
      WHERE OBJ='JQUERY') 
        AND OBJ='JQUERY') AS JQUERY_TOP 
    ,
    (SELECT 
         NM 
       FROM 
       TEST_TABLE 
      WHERE 
      SCORE=(SELECT MAX(SCORE) 
       FROM TEST_TABLE
      WHERE OBJ='SPRING') 
        AND OBJ='SPRING') AS SPRING_TOP 
    ,
    (
     SELECT 
         NM
       FROM
       (
        SELECT NM
              ,AVG(SCORE) AS AVG_SCORE
          FROM TEST_TABLE
          GROUP BY NM
          ORDER BY AVG_SCORE DESC
       )
       WHERE ROWNUM=1
    ) AS AVG_TOP
       
    FROM TEST_TABLE
    WHERE ROWNUM <= 1

2.2 MAX(DECODE)와 RANK OVER() 사용하기 - 1

 SELECT MAX(DECODE(OBJ, 'JAVA'  , NM, NULL)) AS JAVA_TOP
       ,MAX(DECODE(OBJ, 'JSP'  , NM, NULL)) AS JSP_TOP
       ,MAX(DECODE(OBJ, 'ORACLE'  , NM, NULL)) AS ORACLE_TOP
       ,MAX(DECODE(OBJ, 'JQUERY'  , NM, NULL)) AS JQUERY_TOP  
       ,MAX(DECODE(OBJ, 'SPRING'  , NM, NULL)) AS SPRING_TOP
       ,MAX(DECODE(AVG_RANK1, 1 , NM, NULL)) AS AVG_TOP1
       ,MAX(DECODE(AVG_RANK2, 1 , NM, NULL)) AS AVG_TOP2  
   FROM
     (SELECT NM
          , OBJ
          , RANK() OVER(PARTITION BY OBJ ORDER BY SCORE DESC) AS SINGLE_RANK
          , RANK() OVER(PARTITION BY OBJ ORDER BY AVG_SCORE DESC) AS AVG_RANK1
          , DENSE_RANK() OVER(ORDER BY AVG_SCORE DESC) AS AVG_RANK2
       FROM 
       (
        SELECT NM
             , OBJ
             , SCORE
             , AVG(SCORE) OVER(PARTITION BY NM) AS AVG_SCORE
          FROM TEST_TABLE
       )
     ) 
   WHERE SINGLE_RANK = 1
 
 
 
 
 
     (SELECT NM
          , OBJ
          , RANK() OVER(PARTITION BY OBJ ORDER BY SCORE DESC) AS SINGLE_RANK
          , RANK() OVER(PARTITION BY OBJ ORDER BY AVG_SCORE DESC) AS AVG_RANK1
          , DENSE_RANK() OVER(ORDER BY AVG_SCORE DESC) AS AVG_RANK2
       FROM 
       (
        SELECT NM
             , OBJ
             , SCORE
             , AVG(SCORE) OVER(PARTITION BY NM) AS AVG_SCORE
          FROM TEST_TABLE
       )
     ) 

레코드를 특정 column의 값을 기준으로 정렬하여 순서를 매길 수 있다. 

RANK() OVER 또는 ROW_NUMBER() OVER를 사용하면 된다. 

2.3 MAX(DECODE)와 RANK OVER() 사용하기 - 2

WITH TEST_TABLE AS (
   SELECT '상곤' NM, 'JAVA' OBJ, 90 SCORE FROM DUAL UNION ALL
   SELECT '상곤' NM, 'ORACLE' OBJ, 50 SCORE FROM DUAL UNION ALL
   SELECT '상곤' NM, 'JSP' OBJ, 60 SCORE FROM DUAL UNION ALL
   SELECT '상곤' NM, 'SPRING' OBJ, 40 SCORE FROM DUAL UNION ALL
   SELECT '상곤' NM, 'JQUERY' OBJ, 80 SCORE FROM DUAL UNION ALL
   SELECT '건희' NM, 'JAVA' OBJ, 80 SCORE FROM DUAL UNION ALL
   SELECT '건희' NM, 'ORACLE' OBJ, 90 SCORE FROM DUAL UNION ALL
   SELECT '건희' NM, 'JSP' OBJ, 60 SCORE FROM DUAL UNION ALL
   SELECT '건희' NM, 'SPRING' OBJ, 50 SCORE FROM DUAL UNION ALL
   SELECT '건희' NM, 'JQUERY' OBJ, 40 SCORE FROM DUAL UNION ALL
   SELECT '성민' NM, 'JAVA' OBJ, 40 SCORE FROM DUAL UNION ALL
   SELECT '성민' NM, 'ORACLE' OBJ, 80 SCORE FROM DUAL UNION ALL
   SELECT '성민' NM, 'JSP' OBJ, 90 SCORE FROM DUAL UNION ALL
   SELECT '성민' NM, 'SPRING' OBJ, 70 SCORE FROM DUAL UNION ALL
   SELECT '성민' NM, 'JQUERY' OBJ, 50 SCORE FROM DUAL UNION ALL
   SELECT '승구' NM, 'JAVA' OBJ, 50 SCORE FROM DUAL UNION ALL
   SELECT '승구' NM, 'ORACLE' OBJ, 89 SCORE FROM DUAL UNION ALL
   SELECT '승구' NM, 'JSP' OBJ, 85 SCORE FROM DUAL UNION ALL
   SELECT '승구' NM, 'SPRING' OBJ, 90 SCORE FROM DUAL UNION ALL
   SELECT '승구' NM, 'JQUERY' OBJ, 70 SCORE FROM DUAL UNION ALL
   SELECT '상현' NM, 'JAVA' OBJ, 80 SCORE FROM DUAL UNION ALL
   SELECT '상현' NM, 'ORACLE' OBJ, 70 SCORE FROM DUAL UNION ALL
   SELECT '상현' NM, 'JSP' OBJ, 50 SCORE FROM DUAL UNION ALL
   SELECT '상현' NM, 'SPRING' OBJ, 40 SCORE FROM DUAL UNION ALL
   SELECT '상현' NM, 'JQUERY' OBJ, 90 SCORE FROM DUAL
)
SELECT  MAX(DECODE(OBJ, 'JAVA'  , NM, NULL)) AS JAVA_TOP
       ,MAX(DECODE(OBJ, 'JSP'  , NM, NULL)) AS JSP_TOP
       ,MAX(DECODE(OBJ, 'ORACLE'  , NM, NULL)) AS ORACLE_TOP
       ,MAX(DECODE(OBJ, 'JQUERY'  , NM, NULL)) AS JQUERY_TOP  
       ,MAX(DECODE(OBJ, 'SPRING'  , NM, NULL)) AS SPRING_TOP 
       ,MAX(DECODE(AVG_RANK1, 1 , NM, NULL)) AS RANK_OVER 
       ,MAX(DECODE(AVG_RANK2, 1 , NM, NULL)) AS DENSE_RANK 
 FROM(
       SELECT NM
          , OBJ
          , SCORE
          , RANK
          , RANK() OVER (PARTITION BY OBJ ORDER BY AVG_SCORE DESC) AS AVG_RANK1
          , DENSE_RANK() OVER(ORDER BY AVG_SCORE DESC) AS AVG_RANK2
       FROM 
       (
        SELECT NM
             , OBJ
             , SCORE
             , RANK() OVER(PARTITION BY OBJ ORDER BY SCORE DESC) AS RANK
             , AVG(SCORE) OVER(PARTITION BY NM) AS AVG_SCORE
          FROM TEST_TABLE
       )
      )
    WHERE RANK = 1