【方法一 运用临时表】
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