熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> MySQL >> 正文

SQLServer建立交叉表查詢

2013-11-23 21:06:03  來源: MySQL 

  /*
使用方法直接執行傳入參數(series_guid 查詢條件)返回一個數據集

查詢該系列Cylindrical下所有產品
dboP_GetSeriesProductDetail Cylindrical
查詢系列Cylindrical下含有BK的產品
dboP_GetSeriesProductDetail Cylindricalproduct_name like %BK%
*/
CREATE PROCEDURE P_GetSeriesProductDetail(@series_guid varchar() @condition varchar())
AS
DECLARE @ParamNo nvarchar()
DECLARE @SQL nvarchar()
Set @SQL=
DECLARE P_cursor CURSOR
local
fast_forward
FOR SELECT param_no FROM V_product_params where series_guid=@series_guid
OPEN P_cursor

  
FETCH next FROM P_cursor INTO @ParamNo
WHILE (@@fetch_status = )
BEGIN
Set @SQL = @SQL + MAX(CASE param_no WHEN + @ParamNo + THEN param_value ELSE END) AS F + @ParamNo + char()
FETCH next FROM P_cursor INTO @ParamNo
END

  
CLOSE P_cursor
DEALLOCATE P_cursor
Set @SQL=SELECT type_guid series_guid product_no product_name + @SQL +
FROM V_product_params WHERE series_guid= + @series_guid +
if (LTrim(@condition)<>)
Set @SQL= @SQL + and + @condition
Set @SQL= @SQL +
GROUP BY type_guid series_guid product_no product_name

  
Print @SQL
Execute sp_executesql @SQL


From:http://tw.wingwit.com/Article/program/MySQL/201311/29566.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.