[SQL] 여러 행 합치기

안녕하세요.

오늘은 SQL에서 특정 조건 아래 여러 행들을 합치는 방법에 대해 알아보겠습니다.

 

 

 특정 직업별로 명단 만들기

▣ 예시 데이터 : emp

empno	ename	job	sal
7902	FORD	ANALYST	3000
7788	SCOTT	ANALYST	3000
7369	SMITH	CLERK	800
7900	JAMES	CLERK	950
7876	ADAMS	CLERK	1100
7934	MILLER	CLERK	1300
7782	CLARK	MANAGER	2450
7698	BLAKE	MANAGER	2850
7566	JONES	MANAGER	2975
7839	KING	PRESIDENT	5000
7654	MARTIN	SALESMAN	1250
7521	WARD	SALESMAN	1250
7844	TURNER	SALESMAN	1500
7499	ALLEN	SALESMAN	1600

 

1) Stuff 함수

=STUFF('문자열', '시작위치', '문자길이', '치환문자')

- MSSQL에서는 STUFF()와 XML문을 활용함.

- FOR XML PATH문은 여러 행(ROW)으로 조회된 데이터를 하나의 String(문자)으로 묶어 하나의 행에서 보여주고자 할 때 사용함.

- 즉, 구분자 ', '를 넣어 SELECT된 행의 자료를 FOR XML PATH('')를 통해 한 열의 구문으로 만들고 SUTFF()를 이용해 맨 앞의 구분자 ', '를 없앰.

SELECT a.job
     , STUFF((SELECT ', ' + ename
                FROM emp
               WHERE job = a.job
                 FOR XML PATH('')), 1, 2, '') JOB_enames
  FROM emp a
 GROUP BY a.job;

 

: 결과 확인하기

- 직업별로 명단을 구분자(, )로 정리함.

SQL 결과 화면

 

2) LISTAGG, WM_CONCAT 함수

=LISTAGG(COL, '구분자') WITHIN GROUP (COL) -- GROUP 내 ORDER BY 사용 가능
=WM_CONCAT(COL)

- Oracle에서는 버전에 따라서 2가지 함수로 여러 행을 합칠 수 있음.

- Oracle 10g ~ 11g R1까지는 WM_CONCAT 함수로, 이후 최신 버전에서는 LISTAGG 함수를 사용하면 됨.

- LISTAGG 함수의 장점은 GROUP() 내 ORDER BY로 ename을 순서대로 넣어줄 수 있음.

--# 1
SELECT job, LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) FROM emp
GROUP BY job

--# 2
SELECT job, WM_CONCAT(ename) FROM emp
GROUP BY job