代码之家 › 专栏 › 技术社区 › StepUp

将数据透视表的两列之和计算到预定义表中?

pivot sum tsql sql-server sql

StepUp · 技术社区 · 7 年前

非常感谢 @JohnCappelletti 正如他展示的如何旋转桌子:

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

上面的代码工作得很好!

不过,我想知道每列的总数。 因此,所需的输出应如下所示:

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

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

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

SUM

2 回复 | 直到 7 年前

John Cappelletti 7 年前

通知

在交叉应用中添加了两个“总计”行
工会结束后,我把身份证改成了“道达尔”

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)

退换商品

D-Shih 7 年前

CTE 携带你的数据透视结果集。 UNION ALL 结合 SUM

;with cte as (
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
)

INSERT @TR
SELECT 
    NULL,
    SUM(Operator1),
    SUM(OC1),
    SUM(Operator2),
    SUM(OC2),
    SUM(Operator3),
    SUM(OC3),
    SUM(Operator4),
    SUM(OC4),
    SUM(Operator5),
    SUM(OC5) 
FROM CTE
UNION ALL
SELECT ID,Operator1,OC1,Operator2,OC2,Operator3,OC3,Operator4,OC4,Operator5,OC5 
FROM cte

sqlfiddle