在 MS SQL Server 2008 中的透视中计算水平总计

Question

我想知道如何计算行小计。

我的表如下所示：

FCode   DMAR15  DMAR02  DMAR13  DMAR06
F83006     292     334     111     152
F83025     272     298      80     140
F83048     166     179      56      57
F83049      27      32      15      17
F83050     105     112      45      53
F83635     139     153      41      41

我的脚本是：

SELECT [FCode],
       [DMAR15],
       [DMAR02],
       [DMAR13],
       [DMAR06],
       [PCVDR41],
       [PCVDR42],
       [CLDP031],
       [CLDP003],
       [CLDP012],
       [CLDP028],
       [CLDP023],
       [CLDP021],
       [CLDP016],
       [CLDP022]
FROM   (SELECT [FCode],
               [Aggregate],
               [QName]
        FROM   [dbo].[tblMiquestResults]
        WHERE  AuditDate = '2012-09-30') AS SourceTable 
PIVOT (AVG (Aggregate) FOR [QName] IN ([DMAR15], [DMAR02], [DMAR13], 
                                       [DMAR06], [PCVDR41], [PCVDR42], 
                                       [CLDP031], [CLDP003], [CLDP012], 
                                       [CLDP028], [CLDP023], [CLDP021], 
                                       [CLDP016], [CLDP022])) AS P 

我想让它看起来像这样：

FCode   DMAR15  DMAR02  DMAR13  DMAR06
F83006     292     334     111     152
F83025     272     298      80     140
F83048     166     179      56      57
F83049      27      32      15      17
F83050     105     112      45      53
F83635     139     153      41      41
Total     1001    1108     348     460

Answer 1

一种方法

SELECT CASE WHEN GROUPING([FCode]) = 1 THEN 'Total' ELSE [FCode] END AS [FCode],
       SUM([DMAR15]) AS DMAR15,
       SUM([DMAR02]) AS [DMAR02]
        /*TODO: Rest of columns*/
FROM   (SELECT [FCode],
               [Aggregate],
               [QName]
        FROM   [tblMiquestResults]) AS SourceTable 
PIVOT (AVG (Aggregate) FOR [QName] IN ([DMAR15], [DMAR02], [DMAR13], 
                                       [DMAR06], [PCVDR41], [PCVDR42], 
                                       [CLDP031], [CLDP003], [CLDP012], 
                                       [CLDP028], [CLDP023], [CLDP021], 
                                       [CLDP016], [CLDP022])) AS P 
GROUP BY GROUPING SETS ((FCode),())

SQL 小提琴

Answer 2

鉴于您上面的表格（不查看您的查询），以下是执行rowsum的查询。

SQLFIDDLE 演示

select a.fcode, a.DMAR15,  a.DMAR02,  
a.DMAR13,  a.DMAR06, (a.DMAR15 +  a.DMAR02 +
a.DMAR13 +  a.DMAR06) as RowSum
from demo a
;
|  FCODE | DMAR15 | DMAR02 | DMAR13 | DMAR06 | ROWSUM |
-------------------------------------------------------
| F83006 |    292 |    334 |    111 |    152 |    889 |
| F83025 |    272 |    298 |     80 |    140 |    790 |
| F83048 |    166 |    179 |     56 |     57 |    458 |
| F83049 |     27 |     32 |     15 |     17 |     91 |
| F83050 |    105 |    112 |     45 |     53 |    315 |
| F83635 |    139 |    153 |     41 |     41 |    374 |