sql拼接同一字段中的数据_SQLSqlserver中如何将一列数据拼接成一个字符串

创建表值函数：

[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

摘自 朱金拖的专栏