[MSSQL] STRING_AGG, FOR XML PATH

 

 

업무를 하다보면 여러 행의 데이터를 하나의 컬럼에 표기해야하는 경우들이 있다.

SQL Server 2017 부터는 STRING_AGG 라는 편한 함수가 지원되는데 해당 함수가 얼마나 편리한 지 함께 비교하여 보기위해 FOR XML PATH 라는 는 것도 함께 알아보도록 하자.

 

Create Data

• 설명글

CREATE TABLE #temp_emp
(
	emp_id varchar(10),
	emp_name varchar(50) ,
	dept_name varchar(10)
) 


INSERT INTO #temp_emp VALUES ('EMP01', '홍길동', '총무부')
INSERT INTO #temp_emp VALUES ('EMP02', '임꺽정', '총무부')
INSERT INTO #temp_emp VALUES ('EMP03', '고길동', '인사부')
INSERT INTO #temp_emp VALUES ('EMP04', '노진구', '인사부')
INSERT INTO #temp_emp VALUES ('EMP05', '왕눈이', '인사부')
INSERT INTO #temp_emp VALUES ('EMP06', '꽃님이', '')


SELECT * FROM #temp_emp

 

#temp_emp 조회

 

STRING_AGG

• SQL Server 2017 이상부터 사용 가능
• 여러 로우의 자료를 하나의 항목에 콤마 (,) 로 구분지어 표기할 때에 적합

SELECT 
    dept_name,
    STRING_AGG(emp_name, ', ') WITHIN GROUP (ORDER BY emp_name ASC) AS EmployeeList
FROM #temp_emp
GROUP BY dept_name

 

string_agg 를 이용한 조회결과

 

부서별로 부서에 포함된 담당자를 하나의 컬럼 ( EmployeeList ) 에 콤마로 (,) 구분지어 모두 표기하였다. 

STRING_AGG(emp_name, ', ') 를 이용하여 이름마다 [ , ] 가 들어가는 것을 확인할 수 있다. 또한 WITHIN GROUP ( ORDER BY ... ) 를 통해 합쳐지는 순서를 정할 수 있다. 예제에서는 이름순으로 정렬을 설정하였다.

 

FOR XML PATH

• 여러 로우의 자료를 하나의 항목에 콤마 (,) 로 구분지어 표기할 때에 적합
• SQL Server 2017 버전보다 낮을때에 적합

SELECT 
    dept_name,
    STUFF((SELECT ',' + emp_name 
           FROM #temp_emp AS E2 
           WHERE E2.dept_name = E1.dept_name 
           FOR XML PATH('')), 1, 1, '') AS EmployeeList
FROM #temp_emp AS E1
GROUP BY dept_name

 

FOR XML PATH 를 이용한 조회결과

 

SQL Server 버전이 달라 STRING_AGG 가 사용할 수 없을경우에는 이와같이 FOR XML PATH 를 이용하여도 동일하게 위와 같이 추출할 수 있다. 

생각보다 로우별의 문자열을 하나로 합치는건 실무에서 자주 쓰이는 방법이다.

쿼리에서 보다시피 FOR XML PATH 는 복잡한 느낌이 있어서 당신의 개발환경이 SQL Server2017 이상이길 응원해본다.

 

 

[MSSQL] CONVERT - 문자열날짜변환