SQLSqlserver中如何将一列数据拼接成一个字符串

SQLSqlserver中如何将一列数据拼接成一个字符串

 

 

CREATE TABLE [dbo].[Dept](
	Id numeric(18), Code varchar(10)
) ON [PRIMARY]

insert into Dept(id,code)  
select 1, 'zhang'  
union all  
select 1, 'yan'  
union all  
select 2, 'zdw'  
union all  
select 2, 'ydj';  

SELECT id,code 
FROM Dept  

SELECT id,(SELECT code+',' FROM Dept WHERE id=A.id FOR XML PATH('XXX')) AS UserList 
FROM Dept A GROUP BY id  

SELECT B.id,LEFT(UserList,LEN(UserList)-1) code FROM (  
SELECT id,(SELECT code+',' FROM Dept WHERE id=A.id FOR XML PATH('')) AS UserList 
FROM Dept A GROUP BY id  
) B  

 

 

创建表值函数：

 

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语句：

 

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