建立表值函數：
[sql]
create function [dbo].[GetAssessmentCity](
@ryear varchar(4), --年份
@rmonth varchar(2) --月份
) returns @GetCity TABLE(id varchar(20),city varchar(20))
as
begin
declare @result varchar(1200) --傳回結果
insert into @GetCity
select 1,a.AssessmentCity from T_COD_SectionInfo a
right join MonthTargetData b on a.section_id=b.section_id
where (report_year = @ryear www.2cto.com
and report_month = @rmonth)
and (value61 = 1
or value62 = 1)
return
end
SQL語句：
[sql]
SELECT b.id,LEFT(cityList,LEN(cityList)-1) city FROM (
SELECT id, (SELECT city+'、' FROM dbo.GetAssessmentCity('2012','01') WHERE id=a.id FOR XML PATH(''))
AS cityList FROM dbo.GetAssessmentCity('2012','01') a group by id) b
簡單示例：
[sql]
declare @T Table(Id numeric(18), Code varchar(10))
insert into @T(id,code)
select 1, 'zhang'
union all
select 1, 'yan'
union all
select 2, 'zdw'
union all
select 2, 'ydj';
www.2cto.com
SELECT B.id,LEFT(UserList,LEN(UserList)-1) code FROM (
SELECT id,(SELECT code+',' FROM @T WHERE id=A.id FOR XML PATH('')) AS UserList FROM @T A GROUP BY id
) B
摘自 朱金拖的專欄