리버's 아름다운 소풍

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

Rowset sting concatenation : Which method is best ?

scalar UDF 방법

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

이 함수의 실제 사용은

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

temp 테이블 사용 방법

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