728x90
병원 전산시스템(HIS)에서는 하나의 검사나 진료 건에 대해 여러 명의 의료진이나 다수의 진단명이 연관된 경우가 많습니다. 이런 경우, 다수 레코드를 하나의 문자열로 병합해서 보여주는 SQL이 필요합니다.
오늘은 Oracle의 XMLELEMENT, XMLAGG 함수를 활용하여 1건의 검사에 여러 검사자 이름을 쉼표로 연결해 한 줄로 표현하는 방법을 소개하겠습니다.
💡 적용 배경 (병원 HIS 예시)
- 한 환자 진료 건에서 여러 진단명이 함께 내려진 경우
- 한 건의 병리 검사를 여러 명의 병리사 또는 검사자가 분담 처리한 경우
- 한 건의 처방 오더에 여러 명의 의료진이 관여한 이력 표시
→ 이런 데이터는 병원 DB에서 1:N 구조로 존재하며, UI 또는 보고서에서 "한 줄로" 표현되어야 합니다.
📌 1. XMLELEMENT / XMLAGG 함수 개념 요약
🔸 XMLELEMENT
XMLELEMENT("태그명", 컬럼)
→ 해당 컬럼 값을 XML 형식으로 감싸줍니다.
🔸 XMLAGG
XMLAGG(XMLELEMENT("태그명", 값들))
→ 여러 XML 요소를 하나의 문자열로 집계합니다.
🏥 병원 예시 테이블 구조
① 검사마스터 (검사 건 정보)
TABLE TEST_MST
( TEST_ID NUMBER, -- 검사 고유번호
PATIENT_ID VARCHAR2(20), -- 환자번호
ORDER_DATE DATE -- 검사 오더일 )② 검사자 목록 (검사자별 담당 정보)
TABLE TEST_PERSON
( TEST_ID NUMBER, -- 검사번호 (TEST_MST와 FK)
WORKER_ID VARCHAR2(10), -- 검사자 사번
ROLE VARCHAR2(20) -- 담당 역할 (ex. 채혈, 분석) )③ 직원 테이블 (사번 → 이름 매핑)
TABLE EMP
( EMP_ID VARCHAR2(10), -- 사번
EMP_NAME VARCHAR2(100) -- 이름 )✅ SQL: 검사자 이름 다수 → 쉼표로 연결된 1개 컬럼으로
SELECT TM.TEST_ID AS 검사번호,
TM.PATIENT_ID AS 환자ID,
TO_CHAR(TM.ORDER_DATE, 'YYYY-MM-DD') AS 검사일자,
SUBSTR(( SELECT XMLAGG
( XMLELEMENT
("A", ',' ||
(SELECT EMP.EMP_NAME
FROM EMP
WHERE EMP.EMP_ID = TP.WORKER_ID) ) ).EXTRACT('//text()').GETSTRINGVAL()
FROM TEST_PERSON TP
WHERE TP.TEST_ID = TM.TEST_ID ), 2) AS 검사자목록
FROM TEST_MST TM;🔍 결과 예시
검사번호환자ID검사일자검사자목록
| 1001 | P1234 | 2024-11-05 | 김현수,이정은,박지호 |
| 1002 | P5678 | 2024-11-06 | 이가은,최용진 |
📌 설명 포인트
- 검사 건 1건당 TEST_PERSON 테이블에 여러 명의 검사자가 있는 구조입니다.
- 각 검사자의 이름은 EMP 테이블을 통해 서브쿼리로 가져오며,
- XMLELEMENT로 각각 XML 포맷으로 감싸고,
- XMLAGG로 병합 후, EXTRACT('//text()')로 XML 태그 제거,
- SUBSTR(..., 2)로 앞 콤마 제거.
Oracle에서는 LISTAGG 함수도 병합에 자주 쓰이지만, 중복 제거가 어려운 경우, 서브쿼리로 이름 등을 끌어와야 할 때 XMLELEMENT + XMLAGG + EXTRACT(...).GETSTRINGVAL() 조합이 훨씬 유연합니다.