다수의 Row 를 구분자를 포함한 Row로 만들기
참조 : http://www.mvpmagazine.net/post/RowAs1Column.aspx
테이블
create table aa (idx int)
create table (bb idx int, namex varchar(10))
데이터
insert into aa values(1)
insert into aa values(2)
insert into bb values(1,'a')
insert into bb values(1,'b')
insert into bb values(1,'c')
insert into bb values(1,'d')
insert into bb values(1,'e')
insert into bb values(2,'a')
insert into bb values(2,'b')
insert into bb values(2,'c')
insert into bb values(2,'d')
insert into bb values(2,'e')
1차
declare @str varchar(1000)
--set @str =''
select @str = coalesce(@str + ',' +cast(bb.namex as varchar(10)),cast(bb.namex as varchar(10)))
from aa join bb on aa.idx = bb.idx
where aa.idx =1
select @str
결과값
a,b,c,d,e
2차
select idx, stuff(
(select ',' + cast(b.namex as varchar)
from aa a join bb b on a.idx = b.idx
where b.idx = aa.idx
group by b.namex, b.idx
order by b.namex
for xml path(''))
,1,1,'') as namex
from aa
where aa.idx in(1,2)
group by idx
결과값
idx namex
1 a,b,c,d,e
2 a,b,c,d,e
--------------------------------------------------------------------------------------------------
SQL 2000 버전
이 예제는 테이블 하나로 테스트 한다.
사용자 함수를 이용한 쿼리
--테이블만든다
create table test
(
id int,
namex varchar(10)
)
go
-- 데이터 넣는다
insert into test values(1,'사과')
insert into test values(1,'배')
insert into test values(1,'딸기')
insert into test values(2,'참외')
insert into test values(3,'수박')
insert into test values(3,'수박')
insert into test values(6,'수박')
go
-- 같은 id 별로 namex 를 '값,값,' 으로 표현 해주는 사용자 함수
CREATE FUNCTION dbo.ConcatResult(@State CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output + ',','') + NAMEX
FROM TEST
WHERE ID = @State
ORDER BY ID
RETURN @Output
END
GO
-- 실제 적용 쿼리
SELECT DISTINCT ID, dbo.ConcatResult(ID) as NAMEX
FROM TEST
ORDER BY ID
--------------------------------------------------------------------------------------------------