获取动态查询选择字段以在Crystal Reports中显示

Question

我有一个我认为是一个相当复杂的查询(至少对我来说(，我决定尝试使用动态SQL来解决它。但是，我有两个问题无法解决。

情况

在表中，用户可以输入具有金额，周和状态的项目。

因此，数据应该类似于这种格式。

            Week 1        Week 2    
---------+-----------+-------------
Status 1 |    50            25
Status 2      10            20

这是SQL中的数据。

Status 1  | Week 1 | 25
Status 1  | Week 1 | 25
Status 1  | Week 2 | 25
Status 2  | Week 1 |  2
Status 2  | Week 1 |  8
Status 2  | Week 1 | 10
Status 2  | Week 1 | 10

对于每个状态i使用动态枢轴表根据几周总和数量。

我尝试了什么：

--EXEC usp_weekReport @weeks=1, @year='2019'
ALTER PROCEDURE usp_weekReport
    (@weeks INT,
     @year NVARCHAR(4))
AS
    DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
    SET @columns = N'';
    SELECT @columns += N', p.' + QUOTENAME([week])
    FROM 
        (SELECT p.[week] 
         FROM [dbo].[Invoices] P
         WHERE DATEPART(YEAR, P.date) = @year
           AND ([week] IN (@weeks)
                OR [week] IN (@weeks + 1)
                OR [week] IN (@weeks + 2)
                OR [week] IN (@weeks + 3)
                OR [week] IN (@weeks + 4)
                OR [week] IN (@weeks + 5))
         GROUP BY P.[week]) AS x;
SET @sql = N'
SELECT p.[statusName],' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT 
    SUM(CAST(REPLACE(REPLACE(A.amount,'','',''''),''$'','''') AS FLOAT)) as sumInvoice,
  A.invoiceStatusID_FK,
  B.statusName,
  [week]
  FROM [dbo].[Invoices] A
  INNER JOIN invoiceStatus B
  ON A.invoiceStatusID_FK=B.invoiceStatusID
 GROUP BY invoiceStatusID_FK,B.statusName,[week]--,C.programme
) AS j
PIVOT
(
  SUM(sumInvoice) FOR [week] IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
--PRINT @sql;
--EXEC sp_executesql @sql;

CREATE TABLE #reportResult
(
statusName nvarchar(50),
weekA INT DEFAULT 0,
weekB int DEFAULT 0
--weekC int DEFAULT 0,
--weekD int DEFAULT 0,
--weekE int DEFAULT 0,
--weekF int DEFAULT 0
)
INSERT #reportResult Exec(@sql)
SELECT statusName, weekA,weekB--,weekC,weekD,weekE,weekF -- here you have "static SELECT with field names"
FROM #reportResult 
DROP TABLE #reportResult

为了解决这个问题，我有上述代码，而在起作用(返回SQL中的值(时，我有两个问题。

问题

我的第一个问题是，我无法将此代码与Crystal Reports中的报告一起使用。当我导入存储过程时，列显示为空白。我查看了以下链接。[从动态SQL查询中选择字段名称] [1]

[1]：从动态SQL查询中选择字段名称，即使我试图在所说的确切工作之后对答案进行建模。它似乎对我不起作用，因为我的数据列仍然在晶体中空白。

我的想法是从单独的存储过程中调用我的第一个存储过程，但是给出了我的答案是如何返回的(不仅是我可以分配给变量的单个值，目前正在考虑返回表值函数(i怀疑那会起作用。

第二个问题是因为我的"周"是动态的(长达6周(，我无法用"备用"列创建临时表，或者我遇到了一个错误(列数不正确(，如您所见，我评论了他们出去了，我也不能使用"选择"

感谢提供的任何帮助或想法。

Answer 1

尝试以下内容，它使用全局温度表：

ALTER PROCEDURE  usp_weekReport 
    @weeks INT,
    @year  NVARCHAR(4)
AS
BEGIN
    DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
    SET @columns = N'';
    SELECT @columns+=N', p.'+QUOTENAME([week])
     FROM (
          SELECT p.[week]
          FROM [dbo].[Invoices] P
          WHERE DATEPART(YEAR, P.date) = @year
                AND ([week] IN(@weeks)
                OR [week] IN(@weeks + 1)
                OR [week] IN(@weeks + 2)
                OR [week] IN(@weeks + 3)
                OR [week] IN(@weeks + 4)
                OR [week] IN(@weeks + 5))
          GROUP BY P.[week]
       ) AS x;
     SET @sql = N'
                SELECT p.[statusName],'+STUFF(@columns, 1, 2, '')+'
                into ##reportResult
                FROM
                (
                  SELECT 
                    SUM(CAST(REPLACE(REPLACE(A.amount,'','',''''),''$'','''') AS FLOAT)) as sumInvoice,
                  A.invoiceStatusID_FK,
                  B.statusName,
                  [week]
                  FROM [dbo].[Invoices] A
                  INNER JOIN invoiceStatus B
                  ON A.invoiceStatusID_FK=B.invoiceStatusID
                 GROUP BY invoiceStatusID_FK,B.statusName,[week]--,C.programme
                ) AS j
                PIVOT
                (
                  SUM(sumInvoice) FOR [week] IN ('+STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')+')
                ) AS p;';
 EXEC sp_executesql @sql;
     SELECT *
     FROM ##reportResult;
     IF OBJECT_ID('tempdb..##reportResult') IS NOT NULL
     BEGIN
      DROP TABLE ##reportResult;
     END;
END

这是一个没有全局温度表的解决方案：

ALTER PROCEDURE  usp_weekReport 
    @weeks INT,
    @year  NVARCHAR(4)
AS
BEGIN
    DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX), @COLCOUNT int, @TableColumns  NVARCHAR(MAX);
    SET @columns = N'';
    SELECT @columns+=N', p.'+QUOTENAME([week])
     FROM (
          SELECT p.[week]
          FROM [dbo].[Invoices] P
          WHERE DATEPART(YEAR, P.date) = @year
                AND ([week] IN(@weeks)
                OR [week] IN(@weeks + 1)
                OR [week] IN(@weeks + 2)
                OR [week] IN(@weeks + 3)
                OR [week] IN(@weeks + 4)
                OR [week] IN(@weeks + 5))
          GROUP BY P.[week]
       ) AS x;
    SELECT @COLCOUNT = count(*)
     FROM (
          SELECT p.[week]
          FROM [dbo].[Invoices] P
          WHERE DATEPART(YEAR, P.date) = @year
                AND ([week] IN(@weeks)
                OR [week] IN(@weeks + 1)
                OR [week] IN(@weeks + 2)
                OR [week] IN(@weeks + 3)
                OR [week] IN(@weeks + 4)
                OR [week] IN(@weeks + 5))
          GROUP BY P.[week]
       ) AS x;

      SELECT @TableColumns = CASE When @COLCOUNT = 1 THEN 'weekA'
                            When @COLCOUNT = 2 THEN 'weekA, weekB'
                            When @COLCOUNT = 3 THEN 'weekA, weekB, weekC'
                            When @COLCOUNT = 4 THEN 'weekA, weekB, weekC, weekD'
                            When @COLCOUNT = 5 THEN 'weekA, weekB, weekC, weekD, weekE'
                            When @COLCOUNT = 6 THEN 'weekA, weekB, weekC, weekD, weekE, weekF'                          
                        end;

      CREATE TABLE #reportResult
       (
          statusName nvarchar(50),
          weekA INT DEFAULT 0,
          weekB int DEFAULT 0,
          weekC int DEFAULT 0,
          weekD int DEFAULT 0,
          weekE int DEFAULT 0,
          weekF int DEFAULT 0
       )

     SET @sql = N'
             INSERT INTO #reportResult (statusName,' + @TableColumns +  ')
                SELECT p.[statusName],'+STUFF(@columns, 1, 2, '')+'
                into ##reportResult
                FROM
                (
                  SELECT 
                    SUM(CAST(REPLACE(REPLACE(A.amount,'','',''''),''$'','''') AS FLOAT)) as sumInvoice,
                  A.invoiceStatusID_FK,
                  B.statusName,
                  [week]
                  FROM [dbo].[Invoices] A
                  INNER JOIN invoiceStatus B
                  ON A.invoiceStatusID_FK=B.invoiceStatusID
                 GROUP BY invoiceStatusID_FK,B.statusName,[week]--,C.programme
                ) AS j
                PIVOT
                (
                  SUM(sumInvoice) FOR [week] IN ('+STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')+')
                ) AS p;';
    EXEC sp_executesql @sql;
     SELECT *
     FROM #reportResult;
     IF OBJECT_ID('tempdb..#reportResult') IS NOT NULL
     BEGIN
      DROP TABLE #reportResult;
     END;
END