分组合并字符串字段

【方法一 运用临时表】

if OBJECT_ID('Tempdb..#Item') is not null
 drop table #Item
create table #Item
 (
 ID int Identity(1,1),
 GroupIndex int default 0,
 [Name] varchar(20)
 )

insert into #Item(GroupIndex ,[Name])
select 1, 'AAA'
union all
select 1, 'BBB'
union all
select 2, '111'
union all
select 2, '222'

-- 放入临时表
if OBJECT_ID('Tempdb..#T') is not null
 drop table #T

select GroupIndex, [Name] = cast([Name] as varchar(500))  -- 字段长度改为500
Into #T
from #Item
order by GroupIndex, [Name]

-- 更新临时表(逐行累加)
declare @GroupIndex int, @Name varchar(500)
update #T set
 @Name = (case when @GroupIndex = GroupIndex then @Name + ', ' + [Name] else [Name] end),
 @GroupIndex = GroupIndex,
 [Name] = @Name

-- Update后的效果
select * from #t

-- 取[Name]最大记录
select GroupIndex, Max([Name]) as [Name] from #t group by GroupIndex

-- 删除临时表
drop table #t
drop table #Item
Go

 

【方法二 用FOR XML PATH】（只适用于Sql2005以上版本) 

参考资料: http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html  灵活运用SQL SERVER FOR XML PATH

if OBJECT_ID('Tempdb..#Item') is not null
 drop table #Item
  
create table #Item
 (
 ID int Identity(1,1),
 GroupIndex int default 0,
 [Name] varchar(20)
 )

insert into #Item(GroupIndex ,[Name])
select 1, 'AAA'
union all
select 1, 'BBB'
union all
select 2, '111'
union all
select 2, '222'

-- GroupBy前
select a.GroupIndex,
 (select Name + ',' from #Item b where b.GroupIndex =a.GroupIndex FOR XML PATH('')) as Name 
from #Item a

-- GroupBy后( 当然也可以用Distinct )
select a.GroupIndex, (select Name + ',' from #Item b where b.GroupIndex =a.GroupIndex FOR XML PATH('')) as Name
from #Item a
group by a.GroupIndex

-- 处理去最后的逗号(,)
select g.GroupIndex, LEFT(g.Name, LEN(g.Name)-1) as Name
from
 (select a.GroupIndex, (select Name + ',' from #Item b where b.GroupIndex =a.GroupIndex FOR XML PATH('')) as Name
 from #Item a
 group by a.GroupIndex) g
 
Go

 【方法三：用函数】

if OBJECT_ID('Item') is not null
 drop table Item
  
create table Item
 (
 ID int Identity(1,1),
 GroupIndex int default 0,
 [Name] varchar(20)
 )

insert into Item(GroupIndex ,[Name])
select 1, 'AAA'
union all
select 1, 'BBB'
union all
select 2, '111'
union all
select 2, '222'
Go

IF OBJECT_ID('dbo.f_str')>0
    DROP FUNCTION dbo.f_str
Go

CREATE FUNCTION f_str
(
    @GroupIndex INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @r VARCHAR(1000)
    SELECT @r = ISNULL(@r+',', '') + Name
    FROM Item
    WHERE GroupIndex = @GroupIndex
    RETURN @r
END
Go

SELECT DISTINCT GroupIndex, dbo.f_str(GroupIndex) Name FROM Item

drop table Item
drop function f_str