如何获取数据透视表每行的最大和最小列数? [英] How to get max and min of columns per row for pivoted 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 varchar(25) NULL  
    , MinPrice DECIMAL(18,3) NULL, MaxPrice DECIMAL(18,3) NULL
    , SumCount DECIMAL(18,3) 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  (0,FName,Price)
                     , (0,'OC'+replace(FName,'Operator',''),OperatorID)
                     , (A.ID,'MinPrice', A.Price)
                     , (A.ID,'MaxPrice', A.Price)
                     , (A.ID,'SumCount', A.OperatorId)
                     , (A.ID,FName,Price)
                     , (A.ID,'OC'+replace(FName,'Operator',''),OperatorID)
             ) B (ID,Item,Value)
     Union All
     Select
         ID=0
         , 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 (MinPrice, MaxPrice, SumCount,Operator1, 
      OC1, Operator2, OC2,  
      Operator3, OC3, Operator4, OC4,  Operator5, OC5) ) AS PivotTable

  SELECT * FROM @TR

上面的代码可以完美地工作，除了MinPrice和MaxPrice是错误的！当前它们是Price的Sum():

The above code works perfectly, except MinPrice and MaxPrice are wrong! Currently they are Sum() of Price:

但是我需要价格的Min()和Price列的Max(). 因此所需的输出应如下所示:

But I need Min() of price and Max() of Price column. So the desired output should looks like this:

如何获取数据透视表的行的价格Min()和Price列的Max()?

How to get Min() of price and Max() of Price column for the row of pivoted table?

推荐答案

戈登是正确的，因为您正在混合聚合，所以条件聚合可能会更有效.

Gordon is correct, since you are mixing aggregations, the conditional aggregation may be more performant.

但是，通过添加几个UNION ALLs我们可以获得预期的结果

However, by adding a couple of UNION ALLs we can get the desired results

示例

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  
    , MinPrice DECIMAL(18,3) NULL, MaxPrice DECIMAL(18,3) NULL
    , SumCount DECIMAL(18,3) 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  (0,FName,Price)
                        , (0,'OC'+replace(FName,'Operator',''),OperatorID)
                        , (A.ID,'SumCount', A.OperatorId)
                        , (A.ID,FName,Price)
                        , (A.ID,'OC'+replace(FName,'Operator',''),OperatorID)
             ) B (ID,Item,Value)
     Union All 
     Select ID,Item='MinPrice',Value=min(Price) From @OperatorPrice Group By ID
     Union All
     Select ID,Item='MaxPrice',Value=max(Price) From @OperatorPrice Group By ID
     Union All
     Select 0,Item='MinPrice',Value=min(Price) From @OperatorPrice 
     Union All
     Select 0,Item='MaxPrice',Value=max(Price) From @OperatorPrice
     Union All
     Select 0,Item='SumCount',Value=sum(OperatorId) From @OperatorPrice
     Union All
     Select
         ID=0
         , 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 (MinPrice, MaxPrice, SumCount,Operator1, 
      OC1, Operator2, OC2,  
      Operator3, OC3, Operator4, OC4,  Operator5, OC5) ) AS PivotTable

  SELECT * FROM @TR

返回

注意:我添加了ID为0的总最小值，最大值，总计数

Note: I added the Total Min, Max, SumCount for ID 0

这篇关于如何获取数据透视表每行的最大和最小列数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！