막 짠거라서 ^^;; 참고하시면 좋을거 같아요. 문자열+숫자는 형을 변환시켜주셔야 합니다.

create table test_TBL_99

(

 col1 int

,col2 char(10)

,col3  int

)

insert into test_TBL_99 values (1,'주형권',99)

select convert(char(10),col1)+col2+convert(char(10),col3)

from test_TBL_99

for xml path를 이용해서 만들어 볼수 있겠네요,,

;with cteMaster (cMasterNo, cName, cAge, cAddr) as (
	select '0001', N'김철수', 54, N'서울시 등촌동' union all
	select '0002', N'박상길', 50, N'서울시 목동'
)
, cteDetail (cMasterNo, cDetailNo, cName, cAge, cAddr) as (
	select '0001', '0000001', N'아들1', 12, N'서울시 반포동' union all
	select '0001', '0000002', N'아들2', 10, N'서울시 반포동 2' union all
	select '0002', '0000003', N'딸1', 20, N'수원시 구운동'
)
select	*
	,	stuff
		(	(	select	'|'+ cDetailNo +','+ cName +','+ rtrim(cAge) +','+ cAddr
				from	cteDetail
				where	cMasterNo = a.cMasterNo
				for xml path('')
			)
		, 1, 1, ''
		) as cDetail
from	cteMaster as a
go