학생 성적 관리에서 시험 단위, 과목 단위로 점수를 저장한다. 예를 들어 2025년 8번째 시험은 “2025008”과 같은 시험코드로 관리할 수 있다. 이번 글에서는 시험코드가 포함된 학생 성적 테이블 구조(종적)를 기준으로, 특정 시험에서 학생별 수학·국어·영어 점수를 조회(횡적)하는 방법을 두 가지 방식(CASE, PIVOT)으로 정리하겠다.
1. 테이블 구조
(1) 학생 (STUDENT)
- 학생번호 (PK)
- 성명
- 반코드
- 성별
- 기타 ……
(2) 학생성적 (STUDENT_SCORE)
- 시험코드 (PK)
- 학생번호 (PK)
- 과목코드 (PK)
- 성적
- 기타 ……
즉, 시험코드 + 학생번호 + 과목코드가 학생성적 테이블의 기본키(PK)이다.
과목코드 예시는 다음과 같다:
<과목코드> : <과목명>
1001 : 수학
1002 : 국어
1003 : 영어
1004 : 물리
1005 : 화학
1006 : 생물
1007 : 지구과학
……
2. CASE 방식
CASE 문과 집계 함수를 사용하면 시험 단위로 행 데이터를 열(Column) 단위로 변환할 수 있다. 성적이 없는 경우는 NVL(…, 0) 처리로 0점을 표시할 수 있다.
SELECT s.학생번호
, s.성명
, NVL(MAX(CASE WHEN g.과목코드 = '1001' THEN g.성적 END), 0) AS 수학성적
, NVL(MAX(CASE WHEN g.과목코드 = '1002' THEN g.성적 END), 0) AS 국어성적
, NVL(MAX(CASE WHEN g.과목코드 = '1003' THEN g.성적 END), 0) AS 영어성적
FROM 학생 s
LEFT JOIN 학생성적 g
ON s.학생번호 = g.학생번호
AND g.시험코드 = '2025008'
AND g.과목코드 IN ('1001','1002','1003')
GROUP BY s.학생번호, s.성명
ORDER BY s.학생번호;3. PIVOT 방식 (오라클 전용)
오라클에서는 PIVOT 절을 사용하면 보다 직관적으로 열 변환을 할 수 있다.
(Oracle Database 11g에서 피벗 연산자를 도입했다. 이 연산자를 사용하면 행을 열로 쉽게 전환할 수 있다.)
SELECT 학생번호
, 성명
, NVL(수학성적, 0) AS 수학성적
, NVL(국어성적, 0) AS 국어성적
, NVL(영어성적, 0) AS 영어성적
FROM
(
SELECT s.학생번호
, s.성명
, g.과목코드
, g.성적
FROM 학생 s
LEFT JOIN 학생성적 g
ON s.학생번호 = g.학생번호
AND g.시험코드 = '2025008'
)
PIVOT
( MAX(성적)
FOR 과목코드 IN ('1001' AS 수학성적, '1002' AS 국어성적, '1003' AS 영어성적)
)
ORDER BY 학생번호;4. 조회 결과 예시
| 학생번호 | 성명 | 수학성적 | 국어성적 | 영어성적 |
|---|---|---|---|---|
| 10001 | 홍길동 | 95 | 87 | 92 |
| 10002 | 김철수 | 88 | 90 | 0 |
| 10003 | 이순이 | 89 | 92 | 90 |
| …. | …. | … | … | … |
5. 정리
(1) CASE 방식
- ANSI SQL과 유사하여 범용성이 높음
- 다른 DBMS에서도 적용 가능
(2) PIVOT 방식
- 오라클 전용 구문
- 가독성이 뛰어나고 쿼리가 더 직관적임
- 성능 최적화
- JOIN 절에 시험코드와 과목코드 조건을 넣어야 불필요한 데이터 접근을 줄이고 성능을 높일 수 있음
특정 시험코드에 대해 학생별 과목 성적을 조회할 때, 범용성을 원하면 CASE 방식, 오라클 환경에 최적화하고 싶으면 PIVOT 방식을 사용하는 것이 좋다.
※ How to convert rows to columns (PIVOT) and back again (UNPIVOT) with SQL in Oracle Database