SQLServer建立交叉表查詢

　　/*
使用方法直接執行傳入參數（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