计算两列到预定义表中的数据透视表的总和? [英] Calc SUM total of pivoted table by two columns into predefined table?

问题描述

非常感谢 @JohnCappelletti ，因为他向我们展示了如何旋转表格:

Big thanks to @JohnCappelletti as he's shown how to pivot a table:

DECLARE @OperatorPrice TABLE (ID INT NOT NULL, OperatorId INT NULL, Price 
    NUMERIC(18,3) NULL, FName VARCHAR(50) NULL)

INSERT INTO @OperatorPrice (
    ID, OperatorId, Price, FName
)
VALUES
   (226, 996, 22954,'Operator1')
, (266, 1016, 79011.2,   'Operator3')
, (112, 1029, 14869,     'Operator4')
, (112, 996, 22954,      'Operator1')
, (93,   1031, 10568.96, 'Operator5')


DECLARE @TR TABLE 
(
ID INT NULL , 
Operator1  DECIMAL(18,3) NULL, OC1  DECIMAL(18,3) NULL, Operator2  DECIMAL(18,3) NULL, 
OC2  DECIMAL(18,3) NULL, Operator3  DECIMAL(18,3) NULL, OC3  DECIMAL(18,3) NULL, 
Operator4  DECIMAL(18,3) NULL, OC4  DECIMAL(18,3) NULL, Operator5  DECIMAL(18,3) NULL, 
OC5  DECIMAL(18,3) NULL
)

INSERT @TR
SELECT *
FROM  (
    Select A.ID
          ,B.*
     From  @OperatorPrice A
     Cross Apply ( values (FName,Price)
                         ,('OC'+replace(FName,'Operator',''),OperatorID)
                 ) B (Item,Value)
    Union All
    Select ID=(select min(ID) From @OperatorPrice)
          ,B.*
     From ( Select Top 50 N=Row_Number() Over (Order By (Select NULL)) 
            From master..spt_values n1 ) A
     Cross Apply ( values (concat('Operator',N),NULL)
                         ,(concat('OC',N),NULL)
                 ) B (Item,Value)
   ) AS SourceTable        
PIVOT  ( sum(Value) FOR Item IN (Operator1, OC1, Operator2, OC2,  
Operator3, OC3, Operator4, OC4,  Operator5, OC5) ) AS PivotTable

上面的代码运行完美！

但是，我想要每一列的总和. 因此所需的输出应如下所示:

However, I would like the total sum for each column. So the desired ouput should looks like this:

ID     Operator1    OC1   Operator2   OC2   Operator3    OC3     Operator4  OC4   Operator5   OC5
Total  45908.000    1992    NULL      NULL    NULL      NULL     NULL      NULL    NULL   NULL
93     NULL         NULL    NULL      NULL    NULL      NULL     NULL      NULL   10568.96  1031
112    22954.000    996.0   NULL      NULL    NULL      NULL     14869.0  1029.000 NULL   NULL   
226    22954.000    996.0   NULL      NULL    NULL      NULL     14869.0  1029.000 NULL   NULL   
266    NULL         NULL    NULL      NULL   79011.200  1016.000    NULL    NULL   NULL    NULL  

或图像:

我尝试使用以下代码:

INSERT @TR
SELECT 
   Total = SUM([Operator1] + [OC1] + [Operator2] + [OC2] + [Operator3] + 
               [OC3]+ [Operator4] + [OC4] + [Operator5] + [OC5])
, *
FROM  (
    Select A.ID
          ,B.*
     From  @OperatorPrice A
     Cross Apply ( values (FName,Price)
                         ,('OC'+replace(FName,'Operator',''),OperatorID)
                 ) B (Item,Value)
    Union All
    Select ID=(select min(ID) From @OperatorPrice)
          ,B.*
     From ( Select Top 50 N=Row_Number() Over (Order By (Select NULL)) From 
     master..spt_values n1 ) A
     Cross Apply ( values (concat('Operator',N),NULL)
                         ,(concat('OC',N),NULL)
                 ) B (Item,Value)
   ) AS SourceTable        
   PIVOT  ( sum(Value) FOR Item IN (Operator1, OC1, Operator2, OC2,  
            Operator3, OC3, Operator4, OC4,  Operator5, OC5) ) AS PivotTable

但是它不起作用，因为它显示了一个错误:

But it doesn't work as it shows an error:

消息8120，级别16，状态1，第24行列"PivotTable.ID"为 在选择列表中无效，因为它既不包含在 聚合函数或GROUP BY子句.

Msg 8120, Level 16, State 1, Line 24 Column 'PivotTable.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

如何获取每一列的SUM并将该行放在第一位?

How can I get SUM of each column and put this row at the first place?

推荐答案

通知

• @TR中的ID现在是一个varchar(25)
• 在CROSS APPLY中添加了两个"TOTAL"行
• 全部合并后，我将ID更改为总计"

.

DECLARE @OperatorPrice TABLE (ID int NOT NULL, OperatorId INT NULL, Price 
    NUMERIC(18,3) NULL, FName VARCHAR(50) NULL)

INSERT INTO @OperatorPrice (
    ID, OperatorId, Price, FName
)
VALUES
   (226, 996, 22954,'Operator1')
, (266, 1016, 79011.2,   'Operator3')
, (112, 1029, 14869,     'Operator4')
, (112, 996, 22954,      'Operator1')
, (93,   1031, 10568.96, 'Operator5')


DECLARE @TR TABLE 
(
ID varchar(25) NULL , 
Operator1  DECIMAL(18,3) NULL, OC1  DECIMAL(18,3) NULL, Operator2  DECIMAL(18,3) NULL, 
OC2  DECIMAL(18,3) NULL, Operator3  DECIMAL(18,3) NULL, OC3  DECIMAL(18,3) NULL, 
Operator4  DECIMAL(18,3) NULL, OC4  DECIMAL(18,3) NULL, Operator5  DECIMAL(18,3) NULL, 
OC5  DECIMAL(18,3) NULL
)

INSERT @TR
SELECT *
FROM  (
    Select B.*
     From  @OperatorPrice A
     Cross Apply ( values ('Total',FName,Price)
                         ,('Total','OC'+replace(FName,'Operator',''),OperatorID)
                         ,(convert(varchar(25),A.ID),FName,Price)
                         ,(convert(varchar(25),A.ID),'OC'+replace(FName,'Operator',''),OperatorID)
                 ) B (ID,Item,Value)
    Union All
    Select ID='Total'
          ,B.*
     From ( Select Top 50 N=Row_Number() Over (Order By (Select NULL)) 
            From master..spt_values n1 ) A
     Cross Apply ( values (concat('Operator',N),NULL)
                         ,(concat('OC',N),NULL)
                 ) B (Item,Value)
   ) AS SourceTable        
PIVOT  ( sum(Value) FOR Item IN (Operator1, OC1, Operator2, OC2,  
Operator3, OC3, Operator4, OC4,  Operator5, OC5) ) AS PivotTable

Select * from @TR
 Order by try_convert(int,ID)

返回

这篇关于计算两列到预定义表中的数据透视表的总和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！