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

<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 Table 사용>
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