将两列转换为预定义表的总和计算总和?

2022-01-09 00:00:00 pivot sql sum tsql sql-server

非常感谢

我尝试使用以下代码:

插入@TR选择总计 = SUM([Operator1] + [OC1] + [Operator2] + [OC2] + [Operator3] +[OC3]+ [Operator4] + [OC4] + [Operator5] + [OC5]), *从  (选择 A.ID,B.*来自@OperatorPrice A交叉应用(值(FName，Price),('OC'+replace(FName,'Operator',''),OperatorID))B(项目，价值)联合所有选择 ID=(从@OperatorPrice 中选择 min(ID)),B.*From (Select Top 50 N=Row_Number() Over (Order By (Select NULL)) Frommaster..spt_values n1 ) A交叉应用(值(concat('Operator'，N)，NULL),(concat('OC',N),NULL))B(项目，价值)) 作为源表PIVOT ( sum(Value) FOR Item IN (Operator1, OC1, Operator2, OC2,Operator3, OC3, Operator4, OC4, Operator5, OC5) ) 作为数据透视表

但它不起作用，因为它显示错误:

<块引用>

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

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

解决方案

通知

  • @TR 中的 ID 现在是 varchar(25)
  • 在 CROSS APPLY 中添加了两个TOTAL"行
  • 在Union All之后，我把ID改成了Total"

.

DECLARE @OperatorPrice TABLE (ID int NOT NULL, OperatorId INT NULL, PriceNUMERIC(18,3) NULL，FName VARCHAR(50) NULL)插入@OperatorPrice (ID、OperatorId、价格、FName)价值观(226, 996, 22954,'操作员1'), (266, 1016, 79011.2, 'Operator3'), (112, 1029, 14869, 'Operator4'), (112, 996, 22954, 'Operator1'), (93, 1031, 10568.96, 'Operator5')声明@TR 表(ID varchar(25) NULL ,运算符 1 DECIMAL(18,3) NULL，OC1 DECIMAL(18,3) NULL，运算符 2 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 十进制 (18,3) 空)插入@TR选择 *从  (选择 B.*来自@OperatorPrice A交叉应用(值('Total',FName,Price),('Total','OC'+replace(FName,'Operator',''),OperatorID),(转换(varchar(25),A.ID),FName,Price),(convert(varchar(25),A.ID),'OC'+replace(FName,'Operator',''),OperatorID)) B (ID,项目,价值)联合所有选择 ID='总计',B.*From (Select Top 50 N=Row_Number() Over (Order By (Select NULL))从 master..spt_values n1 ) A交叉应用(值(concat('Operator'，N)，NULL),(concat('OC',N),NULL))B(项目，价值)) 作为源表PIVOT ( sum(Value) FOR Item IN (Operator1, OC1, Operator2, OC2,Operator3, OC3, Operator4, OC4, Operator5, OC5) ) 作为数据透视表从@TR 中选择 *按 try_convert(int,ID) 排序

返回

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

The above code works perfectly!

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

or an image:

I've tried to use the following code:

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:

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.

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

解决方案

Notice

  • The ID in @TR is now a varchar(25)
  • Added two "TOTAL" rows in the CROSS APPLY
  • After the Union All, I changed the ID to "Total"

.

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)

Returns

相关文章