[MsSQL] 여러 행 문자열 합치기 - For Xml Path() / Stuff() 알아가기

Intro

안녕하세요

초보 DBA 다뉴입니다.

 

오늘은 MsSQL의 여러 행의 문자열을 합치는 방법에 대해서 알아보도록 하겠습니다.

---

알아두기

예시 테이블

create table tbnamelist (
  seq int,
  name varchar(30),
  tel varchar(30)
)

insert into tbnamelist values(1, '김수현', NULL)
insert into tbnamelist values(1, '배수지', NULL)
insert into tbnamelist values(1, '홍길동', '000-1234-5678')
insert into tbnamelist values(1, '배수지', '000-4567-9874')
insert into tbnamelist values(2, '김윤아', '000-5678-3456')
insert into tbnamelist values(2, '임꺽정', NULL)
insert into tbnamelist values(3, '박보검', '000-1234-6543')
insert into tbnamelist values(4, '김동현', NULL)
insert into tbnamelist values(4, '이지은', '000-0025-2054')

예시 테이블로 tbnamelist라는 테이블과 데이터를 넣어두고 설명하도록 하겠습니다.

 

Stuff()

입력한 문자열의 특정 위치와 길이를 지정하여 다른 문자로 치환 하는 함수입니다.

Stuff(문자열, 위치, 길이, 치환 할 문자)의 형태로 사용합니다.

select seq, 
       stuff(name, 1, 1, 'A') as stuff_name 
  from tbnamelist

stuff() 예제

name이라는 문자열 컬럼의 위치 1, 길이 1만큼의 문자를 A라는 문자로 변경하였습니다.

For Xml Path()

실행 한 쿼리의 결과를 Xml 형태로 표현하여 반환합니다.

For Xml Path([Row Element Name]) 의 형태로 사용합니다.

select * 
  from tbnamelist
   for xml path

For Xml Path 에 [Row Element Name] 옵션이 안들어갈경우 <row> 로 감싸진다.

select * 
  from tbnamelist
   for xml path('row_replace')

[Row Element Name]옵션을 줄경우 해당 명칭으로 바뀐다.

반응형

---

여러 행의 문자열 합치기

예제 테이블 tbnamelist를 통하여,

같은 Seq컬럼의 Name컬럼 중복제거한 Name컬럼의 가나다 역순으로 정렬하여 문자열을 합치는 예제 진행하겠습니다.

 

순서대로 따라하며 중복 제거, 정렬 등을 중점적으로 보면 좋을 것 같습니다.

1. For Xml Path 적용

select * 
  from tbnamelist
   for xml path

XML형태로 데이터를 표현합니다.

2. 필요 컬럼 Select, Row Element Name 옵션으로 정리

select name
  from tbnamelist
   for xml path('')

Row Element Name 옵션을 '' (빈문자)로 주어 <row></row>가 빈값으로 치환되어 사라졌습니다.

3. 행 구분자 추가하기 - 합친 문자 구분용

select '/' + name 
  from tbnamelist
   for xml path('')

각 행의 name 컬럼의 문자가 합쳐질 때 각 행들을 구분하기 위해 넣을 구분자 '/' 를 추가하였습니다.

추가하며 컬럼의 명칭이 null값이 되어 <name></name> 컬럼명이 사라졌습니다.

4. Stuff()를 사용하여 맨 앞의 구분자 제거

select stuff((select '/' + name 
                from tbnamelist
                 for xml path('')), 1, 1, '')

stuff() 함수를 사용하여 문자열의 첫 구분자 '/' 를 ''(빈문자)로 치환하였습니다.

5. 같은 seq 끼리 묶기

select a.seq,
       stuff((select '/' + name 
                from tbnamelist
               where seq = a.seq
                 for xml path('')), 1, 1, '')
  from tbnamelist as a

여기서 같은 행이 여러개 나오는 이유는 a라고 별칭 준 테이블의 행 개수입니다.

 

아래처럼 name 컬럼을 추가하여 보면 이해가 쉬울 것 입니다. (참고용)

select a.seq,
       a.name,
       stuff((select '/' + name 
                from tbnamelist
               where seq = a.seq
                 for xml path('')), 1, 1, '')
  from tbnamelist as a

6. 동일 행 제거 - distinct

select distinct 
       a.seq,
       stuff((select '/' + name 
                from tbnamelist
               where seq = a.seq
                 for xml path('')), 1, 1, '')
  from tbnamelist as a

distinct 를 통하여 동일한 값의 행을 중복제거 하여 정리하였습니다.

각 seq 마다 1개의 행이 나오게 됩니다.

7. 동일한 name 제거 및 정렬

select distinct 
       a.seq,
       stuff((select '/' + name 
                from tbnamelist
               where seq = a.seq
               group by name
               order by name desc
                 for xml path('')), 1, 1, '')
  from tbnamelist as a

stuff() 함수 안에 있는 Select문의 중복을 제거하고 정렬을 하여 합치는 과정인데요.

 

distinct 를 통해 중복 제거를 할 수도 있는데, group by 를 통하여 중복 name을 제거 하였습니다.

distinct 를 통해 중복 제거를 할 경우, order by 문을 사용할 수 없어 원하는 순서로 정렬을 할 수가 없게됩니다.

 

따라서 group by 로 중복 제거를 한 후 order by 를 통해 정렬을 합니다.

---

Outro

Stuff(), For Xml Path()등에 대해 간단히 알아보았고,

다른 행의 여러 문자열을 합치는 과정에 대해서 알아보았습니다.

 

문자열을 합치는 거의 모든 과정에 대해 순서대로 설명 한 것 같은데요.

해당 과정을 조금씩 필요한 만큼 수정하여, 원하는 값으로 사용하실 수 있을거라 생각이되네요.

 

원하는 데이터를 조합하는데 많은 도움이 되었으면 합니다.

 

이상으로 다뉴였습니다.

감사합니다.