系统开发时会遇到用户要求某一列分组后的数据一起显示而不是分别显示以减少数据重复及方便查看,即是像下面一组数据:
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的排名必然相同,然后再利用循环即可达成此目标。代码如下:
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模式。
或者