您好,UncleToo欢迎您!  为了更好的浏览本站,请使用高版本浏览器
RSS  Tag     设为首页 | 加入收藏
 您所在的位置:首页 > 数据库技术 > SQL Server

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

作者:UncleToo  来源:互联网  日期:2013-09-26 12:09:31
收藏  评论:( 0 )  阅读:1144
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,(<strong>SELECT code+',' FROM Dept WHERE id=A.id FOR XML PATH('XXX')</strong>) 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




除非特别声明,本站所有PHP教程及其他教程/文章均为原创、翻译或网友投稿,版权均归UncleToo中文网所有, 转载请注明作者及出处。
原文网址:http://www.uncletoo.com/html/sqlserver/363.html
读完这篇文章后,你是否有所收获? 分享是一种生活的信念!
  • 0
  • 0
我来说两句
更多>>网友评论