SQLSERVER 列转置的存储过程

有这样一个需求，100多个商品，1万多家店，原表：一个商品一家店一条记录。

要求返回以店为行，商品为列的结果集。

（由于商品个数是不能确定，今天有100个商品，明天可能有150个商品，不能建立固定的表，只做结果集查看。）

在SQLSERVER2005及以后的版本中，用存储过程还是很好实现的，有 for xml path 和 PIVOT两个函数，正好能秒杀之。

 

构建原始表如下：

ProductKey为商品，CustomerKey代表商店，另外有时间和另一个计算列。

 

1. 首先用distinct取出所有的商品，以行显示，这是之后要作为列的。用到了for xml path

declare @allproduct varchar(max)with cte as(select distinct [ProductKey] from ProductAndShop ),cte2(allproduct) as(select ',['+convert(varchar(255),[ProductKey])+']' from cte order by [ProductKey] for xml path('') )SELECT @allproduct= substring(allproduct,2,LEN(allproduct)) from cte2print @allproduct

 

2. 用PIVOT转置就能获得最终结果，你必须要加入一个对计算列的聚合计算，以去除可能得重复。

DECLARE @SQLQUERY VARCHAR(MAX)SET @SQLQUERY='SELECT *FROM ProductAndShop PIVOT ( MAX([UnitPrice]) FOR [ProductKey] IN ( '+@allproduct+' ) ) AS P'exec (@SQLQUERY)

 

在Oracle中，用存储过程返回结果集本身就是很烦的，也不知道有没有for xml path 和 PIVOT的类似函数支持。:P

 

刚去百度了下，Oracle中有对应的函数：

1. wm_concat()相当于for xml path ，详情参见：http://database.51cto.com/art/201010/231126.htm

2. 而PIVOT的用法和SQLSERVER的基本相同

这样的话，推荐用C#去接Oracle的存储过程：http://blog.csdn.net/siegebaoniu/article/details/6536767。