SQL Server - 여러개의 행을 하나의 문자열로 합치기

이 글은 이전 블로그에서 작성한 내용을 옮겨오면서 내용을 추가/수정한 글입니다.

개요

여러개의 행을 하나의 문자열로 합쳐야 할 때 사용하는 구문을 기억용으로 정리합니다.

사용 구문

FOR XML PATH('') → STUFF

select STUFF ((
	selec '구분자' + 컬럼명
	from 테이블
	FOR XML PATH('')), 시작위치정수, 삭제할문자수, 	'변경할 문자' )

사용한 쿼리에 대한 간략 설명

FOR XML : SQL 쿼리의 서식 결과를 XML로 검색
STUFF : 다른 문자열에 문자열을 삽입
( 지정된 시작 위치와 문자 수에 따라 첫 번째 문자열의 문자를 삭제하고 두 번째 문자열을 시작 위치에 삽입 )

예제와 결과

-- 예제를 위한 기초 테이블 생성
declare @TB table(idx int, Title varchar(10))

insert into @TB
values
 (1, '제목1_1')
,(1, '제목1_2')
,(1, '제목1_3')
,(2, '제목2_1')
,(2, '제목2_2')
,(3, '제목3_1')

-- FOR XML PATH('') → STUFF : 여러행, 하나의 문자열로
select	idx
    	,STUFF(
        ( select	' / ' + Title -- ' / ' 구분자 사용
            from	@TB as TB
            where	idx = tb.idx
            FOR XML PATH('')), 1, 3, '') as Titles
from	@TB tb
group by idx

/* -- 결과
idx         Titles
----------- --------------------------------------------------------------
1           제목1_1 / 제목1_2 / 제목1_3 / 제목2_1 / 제목2_2 / 제목3_1
2           제목1_1 / 제목1_2 / 제목1_3 / 제목2_1 / 제목2_2 / 제목3_1
3           제목1_1 / 제목1_2 / 제목1_3 / 제목2_1 / 제목2_2 / 제목3_1
*/

위에서 사용하는 구문에 대해 조금 더 알아보기로 합니다.

FOR XML

FOR XML PATH

중첩된 FOR XML 쿼리 기능과 함께, 간단한 방식으로 EXPLICIT 모드의 유연성을 제공

열 합치기

select	' / ' + Title -- 단일 컬럼
from	@TB as TB
FOR XML PATH('') -- element 태그 없이

/* -- 결과 XML
/ 제목1_1 / 제목1_2 / 제목1_3 / 제목2_1 / 제목2_2 / 제목3_1
*/

element 형식으로 반환

select	*
from	@TB as TB
FOR XML PATH

/* -- 결과 XML
<row>
  <idx>1</idx>
  <Title>제목1_1</Title>
</row>
<row>
  <idx>1</idx>
  <Title>제목1_2</Title>
</row>
<row>
  <idx>1</idx>
  <Title>제목1_3</Title>
</row>
<row>
  <idx>2</idx>
  <Title>제목2_1</Title>
</row>
<row>
  <idx>2</idx>
  <Title>제목2_2</Title>
</row>
<row>
  <idx>3</idx>
  <Title>제목3_1</Title>
</row>
*/

FOR XML RAW

반환된 행들을 행(row)당 단일 <row> element를 생성

select	*
from	@TB as TB
FOR XML RAW

/* -- 결과 XML
<row idx="1" Title="제목1_1" />
<row idx="1" Title="제목1_2" />
<row idx="1" Title="제목1_3" />
<row idx="2" Title="제목2_1" />
<row idx="2" Title="제목2_2" />
<row idx="3" Title="제목3_1" />
*/

FOR XML AUTO

select문을 기반하여 추론된 중첩된 XML 결과를 생성, XML 형태(shape)는 최소한으로 제어 가능

select	*
from	@TB as TB
FOR XML AUTO

/* -- 결과 XML
<TB idx="1" Title="제목1_1" />
<TB idx="1" Title="제목1_2" />
<TB idx="1" Title="제목1_3" />
<TB idx="2" Title="제목2_1" />
<TB idx="2" Title="제목2_2" />
<TB idx="3" Title="제목3_1" />
*/

FOR XML EXPLICIT

XML 형태(shape) 제어가 더 많이 가능 (attribute, element를 원하는 데로 혼합 가능)

select	1 as Tag
    ,NULL as Parent
		,idx as [MainElement!1!idx]
		,NULL as [MainElement!1!Title]
		,NULL as [SubElement!2!Title]
from	@TB as TB
group by idx
UNION ALL
select	2 as Tag
        ,1 as Parent
		,idx
		,Title
		,Title
from	@TB as TB
ORDER BY [MainElement!1!idx], [SubElement!2!Title]
FOR XML EXPLICIT

/* -- 결과 XML
<MainElement idx="1">
  <SubElement Title="제목1_1" />
  <SubElement Title="제목1_2" />
  <SubElement Title="제목1_3" />
</MainElement>
<MainElement idx="2">
  <SubElement Title="제목2_1" />
  <SubElement Title="제목2_2" />
</MainElement>
<MainElement idx="3">
  <SubElement Title="제목3_1" />
</MainElement>
*/

STUFF

문자열의 특정 위치에서 시작하여 지정된 길이만큼의 문자를 다른 문자열로 대체합니다.

STUFF ( character_expression , start , length , replaceWith_expression )

select	STUFF('1_Delete 가 Replace_Text로 변경됨. 뿅!', 1, LEN('1_Delete'), 'Replace_Text')

-- 결과 : Replace_Text 가 Replace_Text로 변경됨. 뿅!

FOR XML PATH('') 로 열 합치기 후, 문자열 시작 부분에 있는 '구분자'를 지우고 싶어 STUFF 사용

SELECT STUFF(
	(	select	' / ' + Title -- ' / ' 구분자 사용
		from	@TB as TB
		FOR XML PATH('')) -- character_expression : 기본 문자열
	, 1	-- start : 시작위치 정수값
	, 3	-- length : 삭제할(구분자) 문자수
	, '' -- replaceWith_expression : 변경할 문자열
)

-- 결과 : 제목1_1 / 제목1_2 / 제목1_3 / 제목2_1 / 제목2_2 / 제목3_1

(위 링크는 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.)

개요