注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

天涯倦客的博客

祝福你朋友永远快乐!

 
 
 

日志

 
 

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

2012-02-29 18:25:31|  分类: asp.net |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
系统开发时会遇到用户要求某一列分组后的数据一起显示而不是分别显示以减少数据重复及方便查看,即是像下面一组数据:
Beverages Chai
Beverages Chang
Beverages Chartreuse verte
Beverages C?te de Blaye
Beverages Guaraná Fantástica
Beverages Ipoh Coffee
Beverages Lakkalik??ri
Beverages Laughing Lumberjack Lager
Beverages Outback Lager
Beverages Rh?nbr?u Klosterbier
Beverages Sasquatch Ale
Beverages Steeleye Stout
Condiments Aniseed Syrup
Condiments Chef Anton's Cajun Seasoning
Condiments Chef Anton's Gumbo Mix
Condiments Genen Shouyu
Condiments Grandma's Boysenberry Spread
Condiments Gula Malacca
Condiments Louisiana Fiery Hot Pepper Sauce
Condiments Louisiana Hot Spiced Okra
Condiments Northwoods Cranberry Sauce
Condiments Original Frankfurter grüne So?e
Condiments Sirop d'érable
Condiments Vegie-spread
Confections Chocolade
Confections Gumb?r Gummib?rchen
Confections Maxilaku
Confections NuNuCa Nu?-Nougat-Creme
Confections Pavlova
Confections Schoggi Schokolade
Confections Scottish Longbreads
Confections Sir Rodney's Marmalade
Confections Sir Rodney's Scones
Confections Tarte au sucre
Confections Teatime Chocolate Biscuits
Confections Valkoinen suklaa
Confections Zaanse koeken
Dairy Products Camembert Pierrot
Dairy Products Flotemysost
Dairy Products Geitost
Dairy Products Gorgonzola Telino
Dairy Products Gudbrandsdalsost
Dairy Products Mascarpone Fabioli
Dairy Products Mozzarella di Giovanni
Dairy Products Queso Cabrales
Dairy Products Queso Manchego La Pastora
Dairy Products Raclette Courdavault
Grains/Cereals Filo Mix
Grains/Cereals Gnocchi di nonna Alice
Grains/Cereals Gustaf's Kn?ckebr?d
Grains/Cereals Ravioli Angelo
Grains/Cereals Singaporean Hokkien Fried Mee
Grains/Cereals Tunnbr?d
Grains/Cereals Wimmers gute Semmelkn?del
Meat/Poultry Alice Mutton
Meat/Poultry Mishi Kobe Niku
Meat/Poultry P?té chinois
Meat/Poultry Perth Pasties
Meat/Poultry Thüringer Rostbratwurst
Meat/Poultry Tourtière
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

显示为如下的效果,该如何做呢?

 

Beverages Chai,Chang,Chartreuse verte,C?te de Blaye,Guaraná Fantástica,Ipoh Coffee,Lakkalik??ri,Laughing Lumberjack Lager,Outback Lager,Rh?nbr?u Klosterbier,Sasquatch Ale,Steeleye Stout
Condiments Aniseed Syrup,Chef Anton's Cajun Seasoning,Chef Anton's Gumbo Mix,Genen Shouyu,Grandma's Boysenberry Spread,Gula Malacca,Louisiana Fiery Hot Pepper Sauce,Louisiana Hot Spiced Okra,Northwoods Cranberry Sauce,Original Frankfurter grüne So?e,Sirop d'érable,Vegie-spread
Confections Chocolade,Gumb?r Gummib?rchen,Maxilaku,NuNuCa Nu?-Nougat-Creme,Pavlova,Schoggi Schokolade,Scottish Longbreads,Sir Rodney's Marmalade,Sir Rodney's Scones,Tarte au sucre,Teatime Chocolate Biscuits,Valkoinen suklaa,Zaanse koeken
Dairy Products Camembert Pierrot,Flotemysost,Geitost,Gorgonzola Telino,Gudbrandsdalsost,Mascarpone Fabioli,Mozzarella di Giovanni,Queso Cabrales,Queso Manchego La Pastora,Raclette Courdavault
Grains/Cereals Filo Mix,Gnocchi di nonna Alice,Gustaf's Kn?ckebr?d,Ravioli Angelo,Singaporean Hokkien Fried Mee,Tunnbr?d,Wimmers gute Semmelkn?del
Meat/Poultry Alice Mutton,Mishi Kobe Niku,P?té chinois,Perth Pasties,Thüringer Rostbratwurst,Tourtière
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的排名必然相同,然后再利用循环即可达成此目标。代码如下:

 

[c-sharp:showcolumns] view plaincopy
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
  1. declare @ProductNames varchar(800);  
  2. select dense_rank() over (order by a.CategoryID) Row_Rank,CategoryName,ProductName,@ProductNames ProductNames  
  3. into #T from Products a join Categories b on a.CategoryID=b.CategoryID;  
  4. declare @Row_Rank int;  
  5. select @Row_Rank=max(Row_Rank),@ProductNames='' from #T  
  6. while @Row_Rank>0  
  7. begin  
  8.     if (select count(*) from #T where Row_Rank=@Row_Rank)=1  
  9.         update #T set ProductNames=ProductName where Row_Rank=@Row_Rank;  
  10.     else  
  11.     begin  
  12.     select @ProductNames=@ProductNames+ProductName+',' from #T where Row_Rank=@Row_Rank order by ProductName;  
  13.         update #T set ProductNames=left(@ProductNames,len(@ProductNames)-1) where Row_Rank=@Row_Rank;  
  14.     end  
  15.     select @Row_Rank=@Row_Rank-1,@ProductNames='';  
  16. end  
  17. select distinct CategoryName,ProductNames from #T order by CategoryName  
  18. drop table #T  

方法二:利用SQL Server 2005对XML的增加支持,其提供了for xml path表达式来实现。

[c-sharp] view plaincopy
  1. select CategoryName,Products=replace(  
  2.  (select ProductName as [data()] from Products b where b.CategoryID=a.CategoryID order by ProductName   
  3.    for xml path('')),' ',',')  
  4. from Categories a order by CategoryName  

或者

[c-sharp] view plaincopy
  1. select CategoryName,Products=left(o.list,len(o.list))  
  2. from Categories a  
  3. cross apply  
  4. (select ProductName+',' as [text()] from Products b where b.CategoryID=a.CategoryID order by ProductName   
  5.    for xml path('')) o(list)  
  6. order by CategoryName  
  评论这张
 
阅读(1142)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017