把数据库中同一列的字符串连接起来的方法介绍

/**
构造用于查询的数据
*/
create table ym
(col1 int, col2 int, col3 varchar(3))
 
insert into ym
select 1, 1, 'A' union all
select 1, 1, 'B' union all
select 1, 2, 'C' union all
select 1, 3, 'D' union all
select 1, 3, 'E'

---查询语句
select a.col1,a.col2,
stuff((select ','+col3 from ym b 
       where b.col1=a.col1 and b.col2=a.col2 
       for xml path('')),1,1,'') 'col3'
from ym a
group by  a.col1,a.col2

/*
col1        col2        col3
----------- ----------- ----------
1           1           A,B
1           2           C
1           3           D,E

(3 行受影响)

*/

--构造用于查询的语句
create table ym (col3 varchar(3))   
insert into ym(col3)
 select 'A' union all
 select 'B' union all
 select 'C' union all
 select 'D'

-- 方法1
declare @r varchar(600)
select @r=isnull(@r,'')+col3 from ym
select @r '结果'

/*
结果
-----------------------
ABCD
(1 row(s) affected)
*/

-- 方法2
select cast(
(select ''+col3 from ym for xml path('')) as varchar(600)) '结果'

/*
结果
-----------------------
ABCD
(1 row(s) affected)
*/

----------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-02-17 16:30:53
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
--------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[title] varchar(4))
insert [huang]
select 1,'测试' union all
select 2,'连接' union all
select 3,'字符'
--------------开始查询--------------------------
SELECT * FROM huang

SELECT DISTINCT
        STUFF(( SELECT  '' + title
                FROM    [huang] b
              FOR
                XML PATH('')
              ), 1, 1, '') 'title'
FROM    [huang]
----------------结果----------------------------
/*
id          title
----------- -----
1           测试
2           连接
3           字符

(3 行受影响)

title
-----------------------------
试连接字符

(1 行受影响)
*/

----------------------------------------------------------------
-- Author  :磊仔
-- Date    :2013-02-17 16:48:12
-- Version: 
--      Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86)  
-- Jun 28 2012 08:42:37  
-- Copyright (c) Microsoft Corporation 
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7600: ) 
--
----------------------------------------------------------------
--> 测试数据：#TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([id] int,[title] varchar(4))
insert #TA
select 1,'测试' union all
select 2,'连接' union all
select 3,'字符'
--------------开始查询--------------------------

--select * from #TA
SELECT 1 id, (select ''+ title from #TA b 
       for xml path('')) title
----------------结果----------------------------
/*  
id          title
----------- --
1           测试连接字符

(1 行受影响)
*/