如何填充额外的行，使每个案例都有15条记录

Question

我有以下存储过程：

select rcl.ReportRunCaseId AS CaseId,
ROW_NUMBER() over(
  PARTITION BY rcl.reportruncaseId 
  ORDER BY rcl.reportruncaseid) as Row, 
YEAR(rce.EcoDate) AS EcoYear,
SUM(rce.NetInv) AS NetInv, 
SUM(rce.NDCash) AS NDCash, 
SUM(rce.DCash) AS DiscCash, 
SUM(rce.GrossGas) AS GrossGas, 
SUM(rce.GrossOil) AS GrossOil, 
SUM(rce.NetProdTax) AS NetProdTax, 
SUM(rce.NetOpCost) AS NetOpCost, 
SUM(rce.NetFixedCost) AS FixedCost, 
SUM(rce.NetTransCost) AS NetTransCost, 
SUM(rce.NetOtherCost) AS NetOtherCost 

From PhdRpt.RptCaseEco_6 rce
INNER JOIN PhdRpt.ReportCaseList_6 rcl 
ON rce.ReportRunCaseId = rcl.ReportRunCaseId 
AND rce.ReportId = 6
inner join PhdRpt.EcoIndicators_6 ei 
ON ei.ReportRunCaseId = rcl.ReportRunCaseId 
where rce.ReportId = 6
group by rcl.ReportRunCaseId, year(rce.EcoDate)

这将产生以下输出：

CaseId    Row     EcoYear ....
   1       1        2005
   1       2        2006
   1       3        2007
   1       4        2008
   1       5        2009
   1       6        2010
   2       1        2005
   3       1        2005
   3       2        2006
   3       3        2007
   4       1        2005
   4       2        2006
   .
   .
   .
   .

我需要为每个CaseId设置15行。其余列中缺少的数据应为null。

我的第一个想法是创建一个行号为1-15的临时表，然后将这些表连接在一起，但它给了我太多的记录。

  IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

CREATE TABLE #temp
(
      Rows CHAR(2)

)

Insert INTO #temp (Rows)
Values (1),(2),(3),(4),(5), 
       (6),(7),(8),(9), (10),
       (11),(12),(13),(14), (15)

我应该如何处理这个问题？友联市?

Answer 1

你在正确的轨道上。我建议为这一点和将来的目的创建一个Numbers表，但这并不重要。获取一个从1到15的数字列表，然后根据您现有的逻辑进行LEFT JOIN。为每个数字返回一条记录，其中包含来自报告表的值。