创建表值函数:
- 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语句:
- 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
简单示例:
- 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
方法二:
- 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
方法三:
- 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