특정쿼리의 결과 레코드를 하나의 문자열, 특히 CSV(Comma Separaed Value) 형태로 바꾸는 방법은 여러가지가 있는데 이를 정리해 보고자 한다.
Rowset sting concatenation : Which method is best ?
scalar UDF 방법
[code SQL]
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
[/code]
이 함수의 실제 사용은
[code sql]
SELECT DISTINCT State, dbo.ConcatAuthors(State)
FROM Authors
ORDER BY State
[/code]
temp 테이블 사용 방법
[code SQL]
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
[/code]