SQL Server 分组及连接组内字符串的几种方法

 系统开发时会遇到用户要求某一列分组后的数据一起显示而不是分别显示以减少数据重复及方便查看，即是像下面一组数据：

Produce	Longlife Tofu
Produce	Manjimup Dried Apples
Produce	R?ssle Sauerkraut
Produce	Tofu
Produce	Uncle Bob's Organic Dried Pears
Seafood	Boston Crab Meat
Seafood	Carnarvon Tigers
Seafood	Escargots de Bourgogne
Seafood	Gravad lax
Seafood	Ikura
Seafood	Inlagd Sill
Seafood	Jack's New England Clam Chowder
Seafood	Konbu
Seafood	Nord-Ost Matjeshering
Seafood	R?d Kaviar
Seafood	Rogede sild
Seafood	Spegesild

显示为如下的效果，该如何做呢？

 

Produce	Longlife Tofu,Manjimup Dried Apples,Rössle Sauerkraut,Tofu,Uncle Bob's Organic Dried Pears
Seafood	Boston Crab Meat,Carnarvon Tigers,Escargots de Bourgogne,Gravad lax,Ikura,Inlagd Sill,Jack's New England Clam Chowder,Konbu,Nord-Ost Matjeshering,Röd Kaviar,Rogede sild,Spegesild

 

这是一个有点棘手的问题。现总结了几种较简单的方法与大家共享之。

方法一：利用同一组中使用Dense_Rank的排名必然相同，然后再利用循环即可达成此目标。代码如下：

 declare @ProductNames varchar(800); select dense_rank() over (order by a.CategoryID) Row_Rank,CategoryName,ProductName,@ProductNames ProductNames into #T from Products a join Categories b on a.CategoryID=b.CategoryID; declare @Row_Rank int; select @Row_Rank=max(Row_Rank),@ProductNames='' from #T while @Row_Rank>0 begin if (select count(*) from #T where Row_Rank=@Row_Rank)=1 update #T set ProductNames=ProductName where Row_Rank=@Row_Rank; else begin select @ProductNames=@ProductNames+ProductName+',' from #T where Row_Rank=@Row_Rank order by ProductName; update #T set ProductNames=left(@ProductNames,len(@ProductNames)-1) where Row_Rank=@Row_Rank; end select @Row_Rank=@Row_Rank-1,@ProductNames=''; end select distinct CategoryName,ProductNames from #T order by CategoryName drop table #T

Produce	Longlife Tofu
Produce	Manjimup Dried Apples
Produce	R?ssle Sauerkraut
Produce	Tofu
Produce	Uncle Bob's Organic Dried Pears
Seafood	Boston Crab Meat
Seafood	Carnarvon Tigers
Seafood	Escargots de Bourgogne
Seafood	Gravad lax
Seafood	Ikura
Seafood	Inlagd Sill
Seafood	Jack's New England Clam Chowder
Seafood	Konbu
Seafood	Nord-Ost Matjeshering
Seafood	R?d Kaviar
Seafood	Rogede sild
Seafood	Spegesild

方法二：利用SQL Server 对XML的支持，其提供的FOR XML选项中的PATH模式。

select CategoryName,Products=replace( (select ProductName as [data()] from Products b where b.CategoryID=a.CategoryID order by ProductName for xml path('')),' ',',') from Categories a order by CategoryName

或者

select CategoryName,Products=left(o.list,len(o.list)) from Categories a cross apply (select ProductName+',' as [text()] from Products b where b.CategoryID=a.CategoryID order by ProductName for xml path('')) o(list) order by CategoryName