[MS Sql] 여러 레코드를 하나의 문자열로 합치기

특정쿼리의 결과 레코드를 하나의 문자열, 특히 CSV(Comma Separaed Value) 형태로 바꾸는 방법은 여러가지가 있는데 이를 정리해 보고자 한다.

Rowset sting concatenation : Which method is best ?

scalar UDF 방법

<BR>USE pubs<BR>GO<BR><BR>CREATE FUNCTION dbo.ConcatAuthors(@State CHAR(2))<BR>RETURNS VARCHAR(8000)<BR>AS<BR>BEGIN<BR>&nbsp;DECLARE @Output VARCHAR(8000)<BR>&nbsp;SET @Output = ''<BR><BR>&nbsp;SELECT @Output = CASE @Output <BR>&nbsp; &nbsp; WHEN '' THEN au_lname <BR>&nbsp; &nbsp; ELSE @Output + ', ' + au_lname <BR>&nbsp; &nbsp; END<BR>&nbsp;FROM Authors<BR>&nbsp;WHERE State = @State<BR>&nbsp;ORDER BY au_lname<BR><BR>&nbsp;RETURN @Output<BR>END<BR>GO<BR>



이 함수의 실제 사용은

<BR>SELECT DISTINCT State, dbo.ConcatAuthors(State)<BR>FROM Authors<BR>ORDER BY State<BR>



temp 테이블 사용 방법

<BR>CREATE TABLE #AuthorConcat<BR>(<BR>&nbsp;State CHAR(2) NOT NULL,<BR>&nbsp;au_lname VARCHAR(8000) NOT NULL,<BR>&nbsp;Ident INT IDENTITY(1,1) NOT NULL PRIMARY KEY<BR>)<BR><BR>INSERT #AuthorConcat <BR>(<BR>&nbsp;State,<BR>&nbsp;au_lname<BR>)<BR>SELECT<BR>&nbsp;State, <BR>&nbsp;au_lname<BR>FROM Authors<BR>ORDER BY <BR>&nbsp;State, <BR>&nbsp;au_lname<BR><BR>DECLARE @Authors VARCHAR(8000)<BR>SET @Authors = ''<BR>DECLARE @State CHAR(2)<BR>SET @State = ''<BR><BR>UPDATE #AuthorConcat<BR>SET @Authors = au_lname = CASE <BR>&nbsp; &nbsp; WHEN @State = State THEN @Authors + ', ' + au_lname <BR>&nbsp; &nbsp; ELSE au_lname END,<BR>&nbsp;@State = State<BR><BR>SELECT State, MAX(au_lname) <BR>FROM #AuthorConcat<BR>GROUP BY State<BR>

No related posts.