[Oracle] 다중 SELECT

<방법 1>

 

WITH DTABLE(NAME, WEEK, GRADE) AS(
    SELECT '이름1', 1, 'A'  FROM DUAL UNION ALL
    SELECT '이름1', 2, 'A+' FROM DUAL UNION ALL
    SELECT '이름2', 1, 'A'  FROM DUAL UNION ALL
    SELECT '이름2', 2, 'B'  FROM DUAL UNION ALL
    SELECT '이름3', 1, 'B'  FROM DUAL UNION ALL
    SELECT '이름3', 2, 'A'  FROM DUAL)
SELECT
      DISTINCT NAME,
      (SELECT GRADE FROM DTABLE WHERE NAME = T.NAME AND WEEK = 1) "1주",
      (SELECT GRADE FROM DTABLE WHERE NAME = T.NAME AND WEEK = 2) "2주"
FROM DTABLE T
ORDER BY NAME

 

 

<방법 2>

 

WITH DTABLE(NAME, WEEK, GRADE) AS(
    SELECT '이름1', 1, 'A+'  FROM DUAL UNION ALL
    SELECT '이름1', 2, 'B'   FROM DUAL UNION ALL
    SELECT '이름2', 1, 'A'   FROM DUAL UNION ALL
    SELECT '이름2', 2, 'B+'  FROM DUAL UNION ALL
    SELECT '이름3', 1, 'C+'  FROM DUAL UNION ALL
    SELECT '이름3', 2, 'C'   FROM DUAL)
SELECT DISTINCT NAME, 
       MAX(DECODE(WEEK, 1, GRADE)) OVER(PARTITION BY NAME) "1주",
       MAX(DECODE(WEEK, 2, GRADE)) OVER(PARTITION BY NAME) "2주"
FROM DTABLE
ORDER BY NAME

 

실행 결과 

 

<기존>

 

	NAME	WEEK	GRADE
1	이름1	1	A+
2	이름1	2	B
3	이름2	1	A
4	이름2	2	B+
5	이름3	1	C+
6	이름3	2	C

<결과>

 

	NAME	1주	2주
1	이름1	A	A+
2	이름2	A	B
3	이름3	B	A