改正存储过程

Question

请看原贴：http://topic.csdn.net/u/20100601/11/d5ead23a-359a-4ef0-b72b-81e2b703d884.html

Answer 1

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ProductName] [nvarchar](10),[SupplyName] [nvarchar](10),[xuqiutime] [datetime],[xuqiu] [int],[gongying] [int],[balance] [decimal](18,2))
INSERT INTO [tb]
SELECT 'No1','深圳公司','2010-05-24','200','200','0' UNION ALL
SELECT 'No1','深圳公司','2010-05-31','300','200','-100' UNION ALL
SELECT 'No1','深圳公司','2010-06-07','400','1000','600' UNION ALL
SELECT 'No1','深圳公司','2010-06-14','800','1000','200' UNION ALL
SELECT 'No1','深圳公司','2010-06-21','800','0','-800' UNION ALL
SELECT 'No1','深圳公司','2010-06-28','500','1000','500' UNION ALL
SELECT 'No2','上海公司','2010-05-24','500','1000','500' UNION ALL
SELECT 'No2','上海公司','2010-05-31','500','1500','1000' UNION ALL
SELECT 'No2','上海公司','2010-06-07','800','1000','200' UNION ALL
SELECT 'No2','上海公司','2010-06-28','1600','1000','-600'--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,xuqiutime,23)) FROM tb GROUP BY [xuqiutime]
SELECT @S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,xuqiutime,23))+',0)'+quotename(convert(VARCHAR,xuqiutime,23)) 
FROM tb GROUP BY [xuqiutime]
EXEC('
    SELECT CASE TYPE WHEN ''xuqiu'' THEN ProductName ELSE '''' END ProductName,
        CASE TYPE WHEN ''xuqiu'' THEN SupplyName ELSE '''' END SupplyName,        
        Data = CASE TYPE WHEN ''xuqiu'' THEN ''需求'' WHEN ''gongying'' THEN ''供应'' ELSE ''差额'' END,
        '+@S1+',Total
    FROM (
        SELECT [ProductName],[SupplyName],
            CONVERT(VARCHAR,xuqiutime,23) xuqiutime,VALUE,TYPE,
            SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total
        FROM (
                 SELECT ProductName, SupplyName, xuqiutime, CAST(xuqiu AS dec(18, 2)) xuqiu, CAST(gongying AS dec(18, 2)) 
                        AS gongying, balance 
                 FROM tb
             ) tb
            UNPIVOT(VALUE FOR TYPE IN(xuqiu,gongying,balance)) B
        ) A
        PIVOT(MAX(VALUE) FOR xuqiutime IN('+@S+')) B
    ORDER BY B.ProductName,CASE TYPE WHEN ''xuqiu'' THEN 1 WHEN ''gongying'' THEN 2 ELSE 3 END
')
/*
ProductName SupplyName Data 2010-05-24                              2010-05-31                              2010-06-07                              2010-06-14                              2010-06-21                              2010-06-28                              Total
----------- ---------- ---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
No1         深圳公司       需求   200.00                                  300.00                                  400.00                                  800.00                                  800.00                                  500.00                                  3000.00
                       供应   200.00                                  200.00                                  1000.00                                 1000.00                                 0.00                                    1000.00                                 3400.00
                       差额   0.00                                    -100.00                                 600.00                                  200.00                                  -800.00                                 500.00                                  400.00
No2         上海公司       需求   500.00                                  500.00                                  800.00                                  0.00                                    0.00                                    1600.00                                 3400.00
                       供应   1000.00                                 1500.00                                 1000.00                                 0.00                                    0.00                                    1000.00                                 4500.00
                       差额   500.00                                  1000.00                                 200.00                                  0.00                                    0.00                                    -600.00                                 1100.00(6 行受影响)*/你看一下这个范例，把另外两列（xuqiu，gongying）也转为与Blance类型一样就可以了

Answer 2

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[Search_TongJiTime]     @date DateTime, --开始时间
    @dateend DateTime --结束时间AS
BEGIN
SET NOCOUNT ON    
DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,Monday,23)) ,
    @S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,Monday,23))+',0)'+quotename(convert(VARCHAR,Monday,23))
FROM VIEW_XuQiuGongYingTongji 
where Monday>=@date and Monday<=@dateend  --这里加时间限制
GROUP BY [Monday]
DECLARE @SQL VARCHAR(MAX)
SET @SQL='
    SELECT CASE TYPE WHEN ''XuQiuNumber'' THEN ProductName ELSE '''' END ProductName,
        CASE TYPE WHEN ''XuQiuNumber'' THEN SupplyName ELSE '''' END SupplyName,        
        Date = CASE TYPE WHEN ''XuQiuNumber'' THEN ''Order Demand'' WHEN ''AlreadyNumber'' THEN ''Schedule'' ELSE ''Balance'' END,
        '+@S1+',Total
    FROM (
        SELECT [ProductName],[SupplyName],
            CONVERT(VARCHAR,Monday,23) Monday,VALUE,TYPE,
            SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total
        FROM (SELECT * FROM VIEW_XuQiuGongYingTongji WHERE Monday>='''+convert(CHAR,@date,23)+''' and Monday<='''+convert(CHAR,@dateend,23)+''') VIEW_XuQiuGongYingTongji
            UNPIVOT(VALUE FOR TYPE IN(CAST(XuQiuNumber AS dec(18,2)) AS XuQiuNumber,CAST(AlreadyNumber AS dec(18,2)) AS AlreadyNumber,CAST(Balance AS dec(18,2)) AS Balance)) B
        ) A
        PIVOT(MAX(VALUE) FOR Monday IN('+@S+')) B 
    ORDER BY B.ProductName,CASE TYPE WHEN ''XuQiuNumber'' THEN 1 WHEN ''AlreadyNumber'' THEN 2 ELSE 3  END 
'
EXEC(@SQL)    
SET NOCOUNT OFF
END我按照你的方法改了，但是调用绑定时出现了：'(' 附近有语法错误。怎么办？

Answer 3

不能在IN（）里面改，要在SELECT * FROM VIEW_XuQiuGongYingTongji 这个*号来改。你仔细看我上面。

Answer 4

好像是这样测试可以，对不对？
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[Search_TongJiTime]     @date DateTime, --开始时间
    @dateend DateTime --结束时间AS
BEGIN
SET NOCOUNT ON    
DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,Monday,23)) ,
    @S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,Monday,23))+',0)'+quotename(convert(VARCHAR,Monday,23))
FROM VIEW_XuQiuGongYingTongji 
where Monday>=@date and Monday<=@dateend  --这里加时间限制
GROUP BY [Monday]
DECLARE @SQL VARCHAR(MAX)
SET @SQL='
    SELECT CASE TYPE WHEN ''XuQiuNumber'' THEN ProductName ELSE '''' END ProductName,
        CASE TYPE WHEN ''XuQiuNumber'' THEN SupplyName ELSE '''' END SupplyName,        
        Date = CASE TYPE WHEN ''XuQiuNumber'' THEN ''Order Demand'' WHEN ''AlreadyNumber'' THEN ''Schedule'' ELSE ''Balance'' END,
        '+@S1+',Total
    FROM (
        SELECT [ProductName],[SupplyName],
            CONVERT(VARCHAR,Monday,23) Monday,VALUE,TYPE,
            SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total
        FROM (SELECT CAST(XuQiuNumber AS dec(18,2)) AS XuQiuNumber,CAST(AlreadyNumber AS dec(18,2)) AS AlreadyNumber,CAST(Balance AS dec(18,2)) AS Balance,Monday,ProductName,SupplyName FROM VIEW_XuQiuGongYingTongji WHERE Monday>='''+convert(CHAR,@date,23)+''' and Monday<='''+convert(CHAR,@dateend,23)+''') VIEW_XuQiuGongYingTongji
            UNPIVOT(VALUE FOR TYPE IN(XuQiuNumber,AlreadyNumber,Balance)) B
        ) A
        PIVOT(MAX(VALUE) FOR Monday IN('+@S+')) B 
    ORDER BY B.ProductName,CASE TYPE WHEN ''XuQiuNumber'' THEN 1 WHEN ''AlreadyNumber'' THEN 2 ELSE 3  END 
'
EXEC(@SQL)    
SET NOCOUNT OFF
END