创建表值函数：
[sql] view plain copy 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 and report_month = @rmonth) and (value61 = 1 or value62 = 1) return end
SQL语句：
[sql] view plain copy 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] view plain copy 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'; 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
方法二：
[sql] view plain copy declare @output varchar(8000) select @output = coalesce(@output + ',' , '') + name from (SELECT DISTINCT(name) FROM a where R_control='国控' and R_type='重金属河流')AS T print @output go
方法三：
[sql] view plain copy DECLARE @STR VARCHAR(8000) SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT(name) FROM a where R_control='国控' and R_type='重金属河流')AS T SELECT @STR go
转自：http://blog.csdn .NET /mh942408056/article/details/7417857
